Geek Answers Handbook

SQL query for parent-child chain

I have one table that can refer to one other member in the table as a parent. This parent can also refer to another row as a parent ... etc.

id col1 col2 parentID 1 foo bar NULL 2 blah boo 1 3 fob far 2 4 wob lob NULL

I would like to return a chain with an identifier. So if id was 3, I would return row 3, row 2 and row 1. If id was 2, I would return row 2 and row 1. If id was 1 or 4, I would just return this row.

Thank you

+4
source share
4 answers

Use a recursive CTE :

 DECLARE @id INT SET @id = 3 ;WITH hierarchy AS ( SELECT t.id, t.parentid FROM YOUR_TABLE t WHERE t.id = @id UNION ALL SELECT x.id, x.parentid FROM YOUR_TABLE x JOIN hierarchy h ON h.parentid = x.id) SELECT h.id FROM hierarchy h

Results:

 id --- 3 2 1
+9
source

Here you go

 SELECT P.cat_id AS parent_cat_id, P.parent_id AS ROOT, P.cat_name AS parent_cat_name, C.parent_id, C.cat_id, C.cat_name FROM categories AS P LEFT OUTER JOIN categories AS C ON C.parent_id=P.cat_id WHERE P.parent_id IS NULL ORDER BY parent_cat_name, cat_name
+1
source

If you are using recursive CTE, be sure to add

 h.parentid <> x.id

in your connection

 JOIN hierarchy h ON h.parentid = x.id)

else you will just be a maximum recursion error as it winds around

+1
source 
 WITH Hierarchy(ChildId, ChildName, Generation, ParentId) AS ( SELECT Id, Name, 0, ParentId FROM UserType AS FirtGeneration WHERE ParentId IS NULL UNION ALL SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId FROM UserType AS NextGeneration INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId ) SELECT * FROM Hierarchy OPTION(MAXRECURSION 32767)
-1
source

More articles:


All Articles