Posted by Erland Sommarskog on 10/19/05 01:03

(patuww@yahoo.com) writes:
> I have found the Common Table Expressions described in SQL 2005 and I
> am not sure if it applies to this situation.
>
> Here are the tables
>
><PRE>
><B>ManagedServer Table</B>
> --IdManagedServer (PK, int, not Null)
> --Name (nvarchar(256), not null)
>
><B>ManagedServerToManagedServer Table</B>
> --IdParentManagedServer (PK, int, not null)
> --IdChildManagedServer (PK, int, not null)
></PRE>
>
> The following will give you the parent
>
> -- Get Managed Server Group Names
> LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
> ms.IdManagedServer = mstms.IdChildManagedServer
> LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
> msg.IdManagedServer
>
> How would you go about getting all of the "parents" in the tree?
> Can this be done with CTEs? Unfortuately all of the examples found are
> joining on itself.

For this type of query, it is also a good idea to post:

o CREATE TABLE statement(s) for the involved table(s).
o INSERT statement with sample data.
o The desired output given the sample.

This makes it easy to copy and paste and post a tested solution.

Judging from the table design, it appears that a child can have many
parents, which makes sort of interesting for the output.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]