Recursive relations - list people with a supervisor and those without a supervisor
I've got an official table, with id and name....
Officials may have supervisors - these are stored in a join table called officialsupervisor with official_id and supervisor_id as foreign keys to the official table.
I can show officials with their supervisor with
SELECT o.official_name Official, p.official_name Supervisor FROM officialsupervisor s, official o, official p WHERE o.official_id = s.official_id AND p.official_id = s.supervisor_official_id
I can list officials that don't have a supervisor with
SELECT o.official_name Official FROM official o WHERE o.official_id NOT IN (SELECT official_id FROM officialsupervisor)
But I can't work out how to list all officials with their supervisor, or a blank if they don't have one. I feel as though this should be quite straightforward!
Thanks for any help.
Try this once:
SELECT o.official_name Official, p.official_name Supervisor FROM official o LEFT JOIN officialsupervisor s ON o.official_id = s.official_id LEFT JOIN official p ON p.official_id = s.supervisor_official_id