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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply