Search code examples
sqlpostgresqljoinsubquery

SQL Column Contains ID of Another Row

Suppose I have a SQL database my_table of the following form where one column contains the id of another row. How can I select both the name column of the given row and the name of the underlying id row?

my_table

For example, selecting ids 1 and 2 via SELECT name, ? FROM my_table WHERE id IN (1, 2) should return:

name, underlying_name
Apple,, 
Pear, Strawberry
Solution

  • Self-join, I presume:

    select a.id, a.name, b.name
from my_table a left join my_table b on b.id = a.underlying_id
order by a.id;