Recursion to get tree hierarchy using SQL

Question

parent_id	child_id	dist
1	1	0
1	2	1
1	3	2
1	4	1
1	5	3
1	6	2
2	2	0
2	3	1
2	5	2
2	6	1
3	3	0
3	5	1
4	4	0
5	5	0
6	6	0

dist is the expected output. i tried solution inividually which works fine but when i try to join sql query it does not return any data

with data_1 as(select child_id  as id ,parent_id as pa_id from parent),
main as(SELECT d1.id as id 
              ,d2.pa_id as pa_id
              ,0 as dist
       FROM data_1 d1 JOIN data_1 d2 ON d1.id = d2.pa_id
       UNION ALL 
       SELECT d3.id as id 
             ,d4.pa_id as pa_id
             ,1+dist as dist
       FROM data_1 d3 JOIN main d4 ON d4.id = d3.id


)

select distinct id,pa_id,dist from main  
--option (maxrecursion 0)

for testing purposes, we need more data at least all rows for 174 — nbk
– nbk, Commented Mar 7, 2023 at 15:00
your query looks more like it is from SQL SERVER are you sure that you tagged it correctly? — nbk
– nbk, Commented Mar 7, 2023 at 15:30
I updated the tags to make it more clear. It can't be MySQL, because MySQL doesn't support <database>.<schema>.<table> identifiers. — Bill Karwin
– Bill Karwin, Commented Mar 7, 2023 at 15:56
Hmm, i don't get your hierarchy. Why does child 6 has two parents 1 and 6? How does one know how the hierarchy looks with two parents — siggemannen
– siggemannen, Commented Mar 7, 2023 at 16:48

T N · Accepted Answer · 2023-03-07 19:43:04Z

I am going to assume that your initial data is equal to all of the results with dist = 1, and that the total collection of nodes of interest is the union of all parent and child node IDs.

I see a few things wrong with your original query.

First I do not see where you are generating a complete list of starting nodes. The input data might have some nodes that exist only as a parent, some only as a child, and some that will be present as both. A complete list can be obtained by querying the UNION of all parent_id and child_id values. (The union will also de-dup them as a side effect.)
Next, your initial dist = 0 part of your recursive CTE seems flawed. Typically, you would not have a join at this point, and for this problem, you would want to list the same ID as both parent and child for dist = 0.
The recursive part of your CTE also has issues. Your join condition d4.id = d3.id is matching child to child. This is also the cause of the infinite recursion. You should be matching the existing CTE child to the parent in the joined table.

The following is a rewrite of the query that included fixes to all of the above:

;WITH Nodes AS (
    SELECT child_id AS id FROM Parent
    UNION
    SELECT parent_id AS id FROM Parent
),
Hier AS (
    SELECT N.id AS parent_id, N.id AS child_id, 0 AS dist
    FROM Nodes N
    UNION ALL
    SELECT H.parent_id, P.child_id, H.dist + 1 AS dist
    FROM Hier H
    JOIN Parent P ON P.parent_id = H.child_id
)
SELECT *
FROM Hier
ORDER BY parent_id, child_id

The results match your requested post.

See this db<>fiddle for a working demo.

Collectives™ on Stack Overflow

Recursion to get tree hierarchy using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related