MySQL如何显示也不匹配where子句的行

Question

我有这个问题:

SELECT `y78f2_students`.`firstname` , `y78f2_students`.`lastName` , `y78f2_students`.`student_id`,`y78f2_attendance`.`is_present`, `y78f2_attendance`.`note`, `y78f2_attendance`.`thedate`  
FROM `y78f2_students`
INNER JOIN `y78f2_enrolls` ON `y78f2_enrolls`.`student_id` = `y78f2_students`.`student_id` 
INNER JOIN `y78f2_attendance` ON `y78f2_attendance`.`student_id` = `y78f2_students`.`student_id` 
WHERE `y78f2_enrolls`.`term` = 'Term 2 2016' 
AND `y78f2_enrolls`.`crn_class` = 'Math1R1' 
and `y78f2_attendance`.`thedate` = '2016-01-24'
ORDER BY thedate desc

此查询仅返回日期为“2016-01-24”的行。目前只有一行:http://i.imgur.com/jRTBqQ0.png

我需要显示 term = 'Term 2 2016' 和 crn_class` = 'Math1R1' 以及尚未设置日期的所有行。换句话说，我想显示类(class)中的所有学生，如果尚未为这些学生设置日期，它将显示为空。这就是我想要的:http://i.imgur.com/jmsuSF5.png

所以总而言之，我需要显示满足子句的行以及日期为空或尚不存在的行。我该如何编写这个查询？

Answer 1

尝试将与连接表相关的条件从查询末尾移动到每个连接的表各自的 ON 子句。此外，如果您想返回 y78f2_attendance 表中尚无行的记录，则该表应该是 LEFT OUTER 联接，而不是 INNER 加入了。

SELECT `y78f2_students`.`firstname` , `y78f2_students`.`lastName`,
    `y78f2_students`.`student_id`,`y78f2_attendance`.`is_present`, 
    `y78f2_attendance`.`note`, `y78f2_attendance`.`thedate`  
FROM `y78f2_students`
INNER JOIN `y78f2_enrolls` ON
    `y78f2_enrolls`.`student_id` = `y78f2_students`.`student_id`
    AND `y78f2_enrolls`.`crn_class` = 'Math1R1'
LEFT OUTER JOIN `y78f2_attendance` ON
    `y78f2_attendance`.`student_id` = `y78f2_students`.`student_id`
    AND (`y78f2_attendance`.`thedate` IS NULL OR `y78f2_attendance`.`thedate` = '2016-01-24')
WHERE `y78f2_enrolls`.`term` = 'Term 2 2016' 
ORDER BY thedate desc