How to show grand parent on sql hierarchy?

Question

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.

Answer 1

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;

Demo