Posted by patuww on 10/18/05 20:03

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.

[Back to original message]