如果具有特定值，请选择全部并添加新列

Question

I have a platforms table that has id, name and parent_id and other columns.

Is it possible to write a query that selects all platforms from the table, and if it has a parent_id which is not 0, an extra column gets added which has the parent_name?

Like:

id    name    parent_id
2     pl1     0
3     pl2     0
4     pl3     2

And the desired query would yield:

id    name    parent_id    parent_name
2     pl1     0
3     pl2     0
4     pl3     2            pl1

Answer 1

You can do this using a left join:

select p.*, pp.name as parent_name
from platforms p left join
     platforms pp
     on p.parent_id = pp.id;