当一个可以为空时，SQL JOIN在两个字段上

Question

I need a little advice on how to create an SQL statement for a particular case in my data. I have two tables with the following fields:

horse_shows table

   horse_shows.showID
   horse_shows.horse_show_date
   horse_shows.horse_show_managerID
   horse_shows.horse_show_secretaryID - this field is allowed to be empty

show_managers table

   show_managers.managerID
   show_managers.name
   show_managers.email

Both horse_show_managerID and horse_show_secretaryID fields in the horse_shows table map to a managerID in the show_managers table. Not all of the shows will have a show secretary, so the horse_show_secretaryID field in the horse_shows table can be blank.

I would like to print the show date, show manager, and show secretary for a show with a specific ID. And if the horse_show_secretaryID field in the horse_shows table is blank, I want it to just print blank for the secretary. So I tried this:

SELECT 
   horse_shows.horse_show_date, 
   show_managers.name, 
   show_secretaries.name 
FROM horse_shows 
   JOIN show_managers ON horse_shows.horse_show_managerID = show_managers.managerID 
   JOIN show_managers as show_secretaries ON horse_shows.horse_show_secretaryID = show_secretaries.managerID 
WHERE horse_shows.showID = 'XYZ';

But, the above statement only returns the row of the show with that showID if it finds a match on both the horse_show_managerID and the horse_show_secretaryID in the show_managers table. If the horse_show_secretaryID field in the horse_shows table is blank (which is a valid condition), the statement returns no rows. I would like it to return the date, manager and (a blank) secretary for the 'XYZ' show when the horse_show_secretaryID field is blank.

Can anyone help me?

Thanks!

Answer 1

Using JOIN on it's own will use an INNER JOIN meaning the join condition must be met. Use LEFT JOIN instead:

SELECT 
   horse_shows.horse_show_date, 
   show_managers.name, 
   show_secretaries.name 
FROM horse_shows 
   LEFT JOIN show_managers ON horse_shows.horse_show_managerID = show_managers.managerID 
   LEFT JOIN show_managers as show_secretaries ON horse_shows.horse_show_secretaryID = show_secretaries.managerID 
WHERE horse_shows.showID = 'XYZ';