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> ManagedServerToManag
edServer Table</B>
-- IdParentManagedServe
r (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  ManagedServerToManag
edServer mstms ON
ms.IdManagedServer = mstms. IdChildManagedServer

LEFT OUTER JOIN ManagedServer msg ON mstms. IdParentManagedServe
r =
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> ManagedServerToManag
edServer Table</B>
> -- IdParentManagedServe
r (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  ManagedServerToManag
edServer mstms ON
> ms.IdManagedServer = mstms. IdChildManagedServer

> LEFT OUTER JOIN ManagedServer msg ON mstms. IdParentManagedServe
r =
> 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