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!
CodePudding user response：
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.)
CodePudding user response：
Try this Query:
select Name, Name as superior from tablename where tablename.ID==tablename.superiorID