Recursive search in database table including from one to N parents for each row

Question

I have the table, which looks like that

 id   parent_id
 1    null
 2    1
 3    2
 4    1
 5    3

For each row in the table I want to retrieve information about its parents(from first to N generation). It means, if row with id = 3 has parent_id = 2 and id = 2 has parent_id = 1, then 3 belongs to 2 and 1 as well.

The result I want to get:

id    multi_level_parent_id
 1    null
 2    1
 3    2
 3    1
 4    1
 5    3
 5    2
 5    1

I assume, that I have to use recursive select. I has written SQL code, but it returns information only about first generation

WITH Rec AS
(
    SELECT *
    FROM MyTable t
    UNION ALL
    SELECT *
    FROM MyTable t
    INNER JOIN Rec r ON t.id = r.parent_id
)

SELECT *
FROM Rec

Does anybody know how to retrieve information, what I need?

You are describing a closure table... Try here: https://stackoverflow.com/questions/12621873/how-can-i-create-a-closure-table-using-data-from-an-adjacency-list You may need to make some slight modifications to get your exact result set — Kevin Suchlicki, Jun 21 '17 at 20:34

score 3 · Accepted Answer · answered Jun 21 '17 at 20:42

This is what you are after:

WITH Rec
     AS (
     SELECT id,
            id AS parent_id,
            0 AS steps
     FROM MyTable  t
     UNION ALL
     SELECT r.id,
            t.parent_id,
            r.steps + 1 AS steps
     FROM MyTable  t
          INNER JOIN Rec r ON t.id = r.parent_id)
     SELECT id,
            parent_id
     FROM MyTable 
     WHERE parent_id IS NULL
     UNION ALL
     SELECT id,
            parent_id
     FROM Rec
     WHERE parent_id <> id
     ORDER BY id;

and the results:

Recursive search in database table including from one to N parents for each row

1 Answers1