Recursive relationships - list people with supervisor and no supervision

Question

I have an official table with id and name ....

Officials can have managers - they are stored in the connection table with the name officialupervisor with official_oid and supervisor_id as foreign keys to the official table.

I can show officials with my supervisor

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 indicate officials who do not have a dispatcher with

  SELECT o.official_name Official FROM official o WHERE o.official_id NOT IN (SELECT official_id FROM officialsupervisor) 

But I can’t understand how to list all the officials with their leader, or empty if they don’t have them. It seems to me that this should be quite simple!

Thanks for any help.

Answer 1

Try 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 

Answer 2

You do not need the recursive query you need, it is to join two tables using OUTER JOINS :

 SELECT o.official_name Official, p.officialname FROM officialsupervisor s Left Outer Join official o on o.official_id = s.official_id Left Outer JOIN official p ON p.official_id = s.supervisor_official_id