Menu
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
801
To find out who that child's parent is, you have to look at the column parent_id , find the same ID number in the id column, and look in that row for the parent's name. In other words, Jim Cliffy has no parents in this table; the value in his parent_id column is NULL .
parent = c. child ) SELECT distinct parent, child , level FROM cte order by level, parent; This will give you all descendants and the level.
The child table would have one or more columns which relate to one or more columns on the parent table. The parent table column(s) must have a primary or unique constraint place on them.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
