Hierarchy Traversal
-
I need to traverse up to the top of a hierarchy given one or more child ID's, and then traverse back down getting all the ID's along the way. I think an example might explain it the best.
IF OBJECT_ID('TempDB..#Site','U') IS NOT NULLDROP TABLE #Site
Create Table #Site
(
Child_ID Int Primary Key
,Parent_ID Int
)
Insert Into #Site Values (1,1)
Insert Into #Site Values (2,2)
Insert Into #Site Values (3,2)
Insert Into #Site Values (4,4)
Insert Into #Site Values (5,3)
Insert Into #Site Values (6,7)
Insert Into #Site Values (7,4)
Insert Into #Site Values (8,2)
Insert Into #Site Values (9,2)
Insert Into #Site Values (10,7)
Hierarchy generated by sample code
1
2
2.3
2.3.5
2.8
2.9
4
4.7
4.7.6
4.7.10
I currently am trying this, knowing we reach the top the hierarchy when the child equals the parent.
With MyCTE(Child_ID,Parent_ID)
As
(
Select
Child_ID
,Parent_ID
From
#Site
Where
Child_ID in (3,10)
UNION ALL
Select
s.Child_ID
,s.Parent_ID
From
#Site S
inner join MyCTE On
S.Parent_ID = MyCTE.Child_ID
)
Select
Child_ID
From
MyCTE
But it doesn't bring in all the ID's required.
Passing in child ID's 3 and 10, I would like ID's 3 = 2,3,5,8,9 and 10 would return 4,6,7,10 back for a results set of 2,3,4,5,6,7,8,9,10 as a table of row.
Explaining it for Child ID 10, Child 10 has 7 as a parent, 7 is also the parent of 6. 7 also has a parent 4 which is the parent of itself and 7.
-
With RootIDs As (
Select Child_ID,Parent_ID
From #Site
Where Child_ID IN (3,10)
UNION ALL
Select a.Child_ID,a.Parent_ID
From #Site a
INNER JOIN RootIDs c ON a.Child_ID=c.Parent_ID
Where c.Child_ID<>c.Parent_ID),
MyCTE(Child_ID,Parent_ID)
As
(
Select
Child_ID
,Parent_ID
From
RootIDs
Where Child_ID=Parent_ID
UNION ALL
Select
s.Child_ID
,s.Parent_ID
From
#Site S
inner join MyCTE On
S.Parent_ID = MyCTE.Child_ID
and S.Parent_ID <> S.Child_ID
)
Select Distinct
Child_ID
From
MyCTE
Order By Child_ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark, this works great! Can I as how or why it works though I can't quite figure it out reading the code (not the code's fault). Is it using nested CTE's?
-
The CTE "RootIDs" traverses up the hierarchy to gets the root IDs. These are then given to your CTE which traverses back down the hierarchy picking up the IDs you want
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply