SQL recursive id nodes

Question

I have a table structure like so

Id     Desc     Node
---------------------
1      A
2      Aa       1
3      Ab       1
4      B
5      Bb       4
6      Bb1      5

these Desc values are presented in a listview to the user, if the user chooses Bb, I want the ID 5 and also the ID 4 becuase thats the root node of that entry, simular to that if the user chooses Bb1, I need ID 6, 5 and 4

I am only able to query one level up, but there could be n levels, so my query at the moment looks like this

SELECT Id 
FROM tbl 
WHERE Desc = 'Bb1' 
   OR Id = (SELECT Node FROM tbl WHERE Desc = 'Bb1');

Possible duplicate of [Find all members in a tree structure](http://stackoverflow.com/questions/40631222/find-all-members-in-a-tree-structure) — Zohar Peled, Jan 17 '17 at 07:13
Possible duplicate of [SQL Server: How to get all child records given a parent id in a self referencing table](http://stackoverflow.com/questions/1686340/sql-server-how-to-get-all-child-records-given-a-parent-id-in-a-self-referencing) — Shakeer Mirza, Jan 17 '17 at 07:22
both of these solutions will give me all child nodes when selecting a root node, I want it the other way around, i want the parent nodes when selecting the child nodes — Markus, Jan 17 '17 at 07:31
In SQL server, the answer to recursive questions is a recursive cte. The details may change, but the basic is always the same. — Zohar Peled, Jan 17 '17 at 07:41

score 0 · Accepted Answer · answered Jan 17 '17 at 07:32

You can do this with Recursive CTE like below

Schema:

CREATE TABLE #TAB (ID INT, DESCS VARCHAR(10), NODE INT)

INSERT INTO #TAB
SELECT 1 AS ID, 'A' DESCS, NULL NODE
UNION ALL
SELECT 2 , 'AA', 1
UNION ALL
SELECT 3, 'AB', 1
UNION ALL
SELECT 4, 'B', NULL
UNION ALL
SELECT 5, 'BB', 4
UNION ALL
SELECT 6, 'BB1', 5

Now do recursive CTE for picking node value and apply it again on #TAB with a Join.

;WITH CTE AS(
SELECT  ID, DESCS, NODE FROM #TAB WHERE ID=6
UNION ALL
SELECT  T.ID, T.DESCS, T.NODE FROM #TAB T 
INNER JOIN CTE C ON T.ID = C.NODE 
)
SELECT * FROM CTE

When you pass 6 to the first query in CTE, the result will be

+----+-------+------+
| ID | DESCS | NODE |
+----+-------+------+
|  6 | BB1   | 5    |
|  5 | BB    | 4    |
|  4 | B     | NULL |
+----+-------+------+

SQL recursive id nodes

1 Answers1