SQL query for parent-child chain

Question

I have a single table that can refer to one other member in the table as a parent. That parent could also refer to one other row as its parent...and so on.

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 the chain given an id. So if the id were 3 I would return row 3, row 2 and row 1. If id was 2 I would return row 2 and row 1. If the id were 1 or 4 I would just return that row.

thank you

Solution 1

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

Solution 2

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

Solution 3

IF you use a recursive CTE, don't forget to add

h.parentid <> x.id

on your join

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

else you will just a The maximum recursion-error since it loops

Comments

[Owais Qureshi]

its koool :) ,such a nice query ,but I think its made for three level hierarchy ?

[Wasted_Coder]

Simple example using Parent-Child notation and Generations, I need to filter based on generations and the the Child-Parent notation makes the sections clear.

[Wasted_Coder]

Link don't work anymore, you can use sqlservertutorial.net for a nice example.

[Wasted_Coder]

Also you can use OPTION(MAXRECURSION 32767) if you know you will hit a limit. See other answer with this included.