Recursive lookup in a database table including one to N parents for each row
I have a table that looks like this:
id parent_id
1 null
2 1
3 2
4 1
5 3
For each row in the table, I want to get information about my parents (1st to Nth generation). This means that if the string c id = 3
has parent_id = 2
and id = 2
has parent_id = 1
, then it 3
also belongs to 2
and 1
.
Result I want to receive:
id multi_level_parent_id
1 null
2 1
3 2
3 1
4 1
5 3
5 2
5 1
I assume I need to use recursive select
. I wrote the SQL code, but it only returns information about the 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 anyone know how to get the information that I need?
source to share
This is what you need:
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:
source to share