|
Home > Archive > Microsoft SQL Server forum > October 2005 > A way to recursively look up hierarchal data using a lookup table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
A way to recursively look up hierarchal data using a lookup table
|
|
| patuww@yahoo.com 2005-10-27, 9:24 am |
| 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.
| |
| Erland Sommarskog 2005-10-27, 9:25 am |
| (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
|
|
|
|
|