A way to recursively look up hierarchal data using a lookup table

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.(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/techin.../2000/books.asp