1

My question is based on the answer given here

 Attendance: 
id | student_id | date       | 
------------------------------
1  | 2          | 2013-10-24 | 

Students:
student_id | student_name   | 
-----------------------------
1          | Johnny Johnson |
2          | Bobby Tables   |
3          | Suzie Smith    |
To get a report of all the kids at your school and their attendance status for the day, you could run a query like:

SELECT s.student_id, s.student_name
,  CASE WHEN a.id IS NOT NULL THEN 'Absent' ELSE 'Present' END as attendance_status
FROM Students s
LEFT JOIN Attendance a on s.student_id=a.student_id
WHERE a.date = '2013-10-24'
Which would return:

Result :
student_id | student_name   | attendance_status |
-------------------------------------------------
1          | Johnny Johnson | Present           |
2          | Bobby Tables   | Absent            |
3          | Suzie Smith    | Present           |

I have a similar query but it is not returning null values in this case present but its only retunring absent

Result that I am getting :
student_id | student_name   | attendance_status |
-------------------------------------------------      |
2          | Bobby Tables   | Absent            |     |

My query looks like 

SELECT
student.StudentID,
student.Name,
CASE WHEN attendance.StudentID IS NOT NULL THEN 'Present' ELSE 'Absent' END as Attendance_Status
FROM
student
Left JOIN attendance ON student.StudentID = attendance.StudentID
where
attendance.Date = '2015-03-30'
Community
  • 1
Ali Shaikh
  • 479
  • 1
  • 6
  • 11

1 Answers1

2

To get desired results attendance.Date = '2015-03-30' should be part of LEFT JOIN, not WHERE: 

SELECT .... 
FROM
student
Left JOIN attendance ON student.StudentID = attendance.StudentID
AND attendance.Date = '2015-03-30'
a1ex07
  • 9,060
  • 3
  • 27
  • 41