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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy