March 9, 2016 at 12:05 pm
Yes you are correct more than one CTE references cannot be used in the recursive query.
Can you suggest other solution which will work..
March 9, 2016 at 2:08 pm
Try this modification instead. Same idea (stopping when a loop is detected) implemented differently:
WITH cteHierarchy
AS (-- Anchor query: root level with all its children
SELECT c.ParentID AS Parent, c.ChildID AS Child, CAST(r.Name + '-->' + cD.Name AS varchar(MAX)) AS ParentChildTree
FROM #Treedesc AS r -- root
INNER JOIN #Tree AS c -- children
ON c.ParentID = r.ID
INNER JOIN #Treedesc AS cD -- children's description
ON cD.ID = c.ChildID
WHERE NOT EXISTS -- verify it's a root: no ascendants
(SELECT *
FROM #Tree AS a -- ascendant
WHERE a.ChildID = r.ID)
-- Recursive part: children of previously added nodes
UNION ALL
SELECT c.ParentID, c.ChildID, p.ParentChildTree + '-->' + cD.Name
FROM cteHierarchy AS p -- parent
INNER JOIN #Tree AS c -- children
ON c.ParentID = p.Child
INNER JOIN #Treedesc AS cD -- children's description
ON cD.ID = c.ChildID
WHERE p.ParentChildTree NOT LIKE '%-->' + cD.Name + '-->%')
SELECT * FROM cteHierarchy;
March 9, 2016 at 2:57 pm
Thanks a lot Hugo..It works perfect...
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply