CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasewhere-in
user1179214
user1179214

Reputation: 11

MySQL how to show rows that also does not match where clause

I have this query:

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

This query returns only the rows where the date is '2016-01-24'. Currently that is just one row: https://i.sstatic.net/gNcZR.png

I need to show all rows where term = 'Term 2 2016' and crn_class` = 'Math1R1' and also where the date is not set as yet. In order words I want to show all students in the class and if the date is not set for these students yet, it will show null. This is what I would like: https://i.sstatic.net/TCUy2.png

So in summary I need to show rows where the clause is met and those where the date would be null or not exist yet. How can I write this query?

Upvotes: 0

Views: 346

Answers (1)

Asaph
Asaph

Reputation: 162801

Try moving the conditions related to the joined tables from the end of the query, up to the table's respective ON clause for each join. Also, if you would like to return records for which no row yet exists in the y78f2_attendance table, that table should be LEFT OUTER joined, not INNER joined.

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

Upvotes: 2

Related Questions