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.

Answers


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

Need Your Help

invalid preview surface android video recording

android android-camera

I have a service, and I try to make the service record video to file.

Wordpress bulk trash action using table inherited from WP Posts List Table

wordpress wordpress-plugin

In a plugin which shows a table of posts of a custom type, I wanted to add the option for bulk actions. Besides the checkboxes I displayed the control for bulk actions, but when I click Apply, noth...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.