LEFT JOIN fails when sub-query returns null
I have this query intended for reporting off of a MySQL database. The query works fine if the sub-query returns some result. However if 开发者_JAVA百科the sub-query returns no results with the error 'column lessonId cannot be null'. I guess this is logical, but I really never want the report to fail (the date is input by the user)...I want it to just output all levels with zeros. Essentially I just need it to ignore the LEFT JOIN if that query returns no results.
So how do I get around this? I tried an IF statement, but I can't get it working. PLEASE HELP:)
SELECT Level.name as levelName,Lesson.name as lessonName, num_at_level
FROM Level
INNER JOIN
`Lesson`
ON Lesson.LevelId = Level.id
LEFT JOIN
(SELECT lessonId as lessonId, count(*) as num_at_level
FROM `has_Lesson`
WHERE dateStarted <= '2010-01-09'
GROUP BY lessonId
) as t_num_at_level
ON lessonId= Lesson.id;
I'd write this query in the following way:
SELECT v.name AS levelName, l.name AS lessonName,
COUNT(h.dateStarted) AS num_at_level
FROM Level v
INNER JOIN Lesson l
ON v.id = l.LevelId
LEFT JOIN has_Lesson h
ON l.id = h.lessonId AND h.dateStarted <= '2010-01-09'
GROUP BY v.id, l.id;
精彩评论