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?

Answer 1

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: