So this is my table:
id |parent_id |name
-------|-----------|--------------------
1| 0|Kevin Gravey
2| 0|John Chen
3| 1|Clark Garvey
4| 2|Halsey Chen
5| 3|Carl Garvey
6| 4|Betty Chen
7| 5|Rick Garvey
8| 6|Donna Chen
10| 5|Chris Garvey
11| 6|Harrington Chen
But I need to make the table look like this:
id |parent_id |name
-------|-----------|--------------------
1| 0|Kevin Gravey
2| 0|John Chen
3| 0|Kevin Gravey
4| 0|John Chen
5| 0|Kevin Gravey
6| 0|John Chen
7| 0|Kevin Gravey
8| 0|John Chen
10| 0|Kevin Gravey
11| 0|John Chen
I used a query from this But instead I'm getting:
id |parent_id |name
-------|-----------|--------------------
1| 0|
2| 0|
3| 1|Kevin Gravey
4| 2|John Chen
5| 3|Clark Garvey
6| 4|Halsey Chen
7| 5|Carl Garvey
8| 6|Betty Chen
10| 5|Rick Garvey
11| 6|Donna Chen
Anybody know what is the query? I'm using postgre.
You're on the right way. Consider using this :
with recursive tree as (
select id,
parent_id,
array[id] as all_parents,
name as parent_name
from hierarchy
where parent_id = 0
union all
select c.id,
p.parent_id,
p.all_parents,
p.parent_name
from hierarchy c
join tree p
on c.parent_id = p.id
and c.id <> all (p.all_parents)
)
select id, parent_id, parent_name
from tree;