yulianto saparudin yulianto saparudin - 4 months ago 12
SQL Question

POSTGRE how to select parent name from hierarchical table

How to select hierarchical table with parent id?
I have a table like this

+-----------+------------+-------------+
| id | parent_id | name |
+-----------+------------+-------------+
|1 | 0 | a |
+-----------+------------+-------------+
|2 | 1 | a1 |
+-----------+------------+-------------+
|3 | 0 | b |
+-----------+------------+-------------+
|4 | 3 | b1 |
+-----------+------------+-------------+
|5 | 3 | b2 |
+-----------+------------+-------------+


and I want to show the table like this

+-----------+------------+-------------+
| id | name | parent |
+-----------+------------+-------------+
|1 | a | NULL |
+-----------+------------+-------------+
|2 | a1 | a |
+-----------+------------+-------------+
|3 | b | NULL |
+-----------+------------+-------------+
|4 | b1 | b |
+-----------+------------+-------------+
|5 | b2 | b |
+-----------+------------+-------------+


Is it possible to create select like this? Anyone know how to create this in Postgre or MySQL, please give me some suggest,

JPG JPG
Answer

Simply use a left join:

select t1.id, t1.name, t2.name as parent
from yourtable t1
left join yourtable t2
on t1.parent_id = t2.id
order by t1.id

Demo Here

Comments