bigste bigste - 1 month ago 6
MySQL Question

multiple lookups in query with possible NULL values

Here's what I have and here's what I'm trying to accomplish:

teams TABLE
TeamID Season Coach1 Coach2
1 2011 35 22
2 2011 27
3 2012 11
4 2013 22 13

staff TABLE
StaffID Nickname
11 Bob
13 Rick
22 Mary
27 Steve
35 Joe

desired OUTPUT:
TeamID Season c1 c2
1 2011 Joe Mary
2 2011 Steve
3 2012 Bob
4 2013 Mary Rick


Here's my current MYSQL query:

SELECT gt.TeamID, gt.Season, gt.Coach1, c1.Nickname c1Nickname, gt.Coach2, c2.Nickname c2Nickname
FROM gladiator_teams as gt, staff as c1, staff as c2
WHERE gt.Coach1=c1.StaffID AND gt.Coach2=c2.StaffID


This returns:

TeamID Season c1 c2
1 2011 Joe Mary
4 2013 Mary Rick


I can't figure out how to modify the query to return rows that have NULL values for Coach 2. I suppose I need an IFNULL function to go somewhere, but I can't figure out where exactly. Any help is greatly appreciated!

Answer

You should use left JOIN

SELECT 
   gt.TeamID
 , gt.Season
 , gt.Coach1
 , c1.Nickname c1Nickname
 , gt.Coach2
 , c2.Nickname c2Nickname
FROM gladiator_teams as gt
LEFT JOIN staff as c1  gt.Coach1=c1.StaffID
LEFT JOIN staff as c2 gt.Coach2=c2.StaffID