How to limit a join when second table may not have relevant rows

Question

I have two tables in a MySQL database, courses and sessions. I'm trying to summarise the entries in sessions for each course each month.

I can summarise the total sessions for each course, no problem using this query:

   SELECT courses.CourseID, 
    SUM(IF( sessions.Duration IS NULL , 0, sessions.Duration)) AS Hrs
    FROM courses
    LEFT JOIN sessions ON courses.CourseID = sessions.CourseID
    WHERE courses.TrainerID = 113
    GROUP BY courses.CourseID

The problem is that not all courses have sessions, so if I try to limit the query like by adding this after the WHERE clause:

AND sessions.SessionDate >= '2009-06-01' AND sessions.SessionDate <= '2009-06-30'

I only get courses which have sessions, but what I want is the courses without sessions to show 0.

I hope that makes sense.

Can anyone help? Thanks.

Answer 1

add the predicate to the join condition...

 SELECT courses.CourseID,     
     SUM(IF( sessions.Duration IS NULL , 0, sessions.Duration)) AS Hrs    
 FROM courses  
    LEFT JOIN sessions 
     ON courses.CourseID = sessions.CourseID
         And sessions.SessionDate 
              Between '2009-06-01' And '2009-06-30'   
 Where courses.TrainerID = 113    
 GROUP BY courses.CourseID

Also, does MySql have Coalesce operator ? If so, you can change to:

 SELECT courses.CourseID,     
     SUM(Coalesce(sessions.Duration, 0)) AS Hrs    
 FROM courses  
    LEFT JOIN sessions 
     ON courses.CourseID = sessions.CourseID
         And sessions.SessionDate 
              Between '2009-06-01' And '2009-06-30'   
 Where courses.TrainerID = 113    
 GROUP BY courses.CourseID