1

I can do a hierarchy with one row result (using a query from my previous question) but I have no idea where to start when it has multiple rows.

So here's my table:

     id|  parent_id|                      name|
-------|-----------|--------------------------|
      1|     [NULL]|             United States|
      2|     [NULL]|                     Japan|
      3|          1|                  New York|
      4|          3|                  Brooklyn|
      5|          6|               Los Angeles|
      6|          1|                California|
      7|          6|                     Dixon|
      8|          2|                     Kyoto|
      9|          2|                     Tokyo|

name which has null parent_id is a grand parent. So, how to make a query which generated result like this?

                   Country|    States/Province|           City|
|-------------------------|-------------------|---------------|
|            United States|           New York|       Brooklyn|
|            United States|         California|    Los Angeles|
|            United States|         California|          Dixon|
|                    Japan|              Kyoto|         [NULL]|
|                    Japan|              Tokyo|         [NULL]|
parker
  • 67
  • 5

1 Answers1

2

Looks like a simple self join on the table because the number of levels you are interested in is limited:

select co.name as country,
       p.name as "State/Province", 
       ct.name as city
from hierarchy co
  left join hierarchy p on p.parent_id = co.id
  left join hierarchy ct on ct.parent_id = p.id
where co.parent_id is null
order by co.name;

Online example: https://rextester.com/UON36358