October 27, 2016 at 2:53 pm
I am currently struggling with a query to recurse up/down a tree. In real life we have a junction table with two FKs pointing to the parent. Both Id and Parent Id are not nullable. Below is an example of what I am trying to depict without complicating it with the parent table.
('Book1',1,2),
('Book1',1,3),
('Book3',3,4),
('Book4',4,5),
('Book6',6,5),
('Book7',7,6)
--Book 2/5 are the top most nodes
DECLARE @id as int = 5
;WITH tree
AS (
SELECT ChildId,
ParentId,
1 TreeLevel
FROM @Temp
WHERE ParentId = @Id
--UNION ALL
--SELECT T.ChildId,
-- Temp.ParentId,
-- TreeLevel + 1
--FROM @Temp Temp
--INNER JOIN tree t
--ON t.ChildId = temp.ParentId
)
Select * FROM tree
If I pass in 5 I really need to somehow get that in the childId field so I can treat it like a recursive query. I can't quite figure out how to get the results like this
5-6,7
5- 4,3,1
October 27, 2016 at 3:32 pm
I think the only problem with your original query was using the ChildID from the wrong table in the second part of the rCTE.
DECLARE @Temp TABLE (descr CHAR(5), ChildID TINYINT, ParentID TINYINT)
INSERT @Temp(descr
, ChildID
, ParentID
)
VALUES
('Book1',1,2),
('Book1',1,3),
('Book3',3,4),
('Book4',4,5),
('Book6',6,5),
('Book7',7,6)
--Book 2/5 are the top most nodes
DECLARE @id as int = 5
;WITH tree
AS (
SELECT ChildId,
ParentId,
1 TreeLevel,
CAST(ParentID AS VARCHAR(MAX)) + '/' + CAST(ChildID AS VARCHAR(MAX)) AS TreePath
FROM @Temp
WHERE ParentId = @Id
--)
UNION ALL
SELECT Temp.ChildID,
Temp.ParentId,
TreeLevel + 1,
TreePath + '/' + CAST(Temp.ChildID AS VARCHAR(MAX))
FROM @Temp Temp
INNER JOIN tree t
ON t.ChildId = temp.ParentId
)
Select * FROM tree
I also added a TreePath to help show the path taken.
Drew
PS: Your question says that there are two parents, but then it says that 2/5 are the topmost nodes. These two statements conflict, so I treated 5 as the parent.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 27, 2016 at 4:48 pm
That is great work. It is exactly what I needed. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply