Search code examples
sqlkey

SQL statement to get column based on ID from same table

The table is like so:

ID Name SupervisorID
1 John NULL
2 Michael NULL
3 David 1

SupervisorID is a foreign key of ID. So, David's SupervisorID of 1 refers to John.

I want to write a statement that retrieves just Name and Supervisor Name, where applicable. So the result-set should look like this:

Name Supervisor
John NULL
Michael NULL
David John

It seems like it should be simple but I can't work it out.

Thank you!

Solution

  • Simply do a self LEFT JOIN:

    select t1.name, t2.name
from tablename t1
left join tablename t2 on t2.ID = t1.SupervisorID

    (Doing a LEFT JOIN to also return people without a supervisor.)