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'

closed as off-topic by Paul White, Mark Sinkinson, RolandoMySQLDBA, RLF, Max Vernon Mar 31 '15 at 17:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mark Sinkinson, RolandoMySQLDBA, RLF, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Note that the answer given in the original question has now been fixed based on the accepted answer here. – Core.B Oct 6 '17 at 14:31
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'
  • Just one more thing if I am not asking too much, maybe you could help me with. If I want to display results for multiple dates but same table how could I do that? – Ali Shaikh Mar 30 '15 at 23:18
  • @ Ali Shaikh: something like AND attendance.Date IN ( '2015-03-30','2015-03-31') should do the job. – a1ex07 Mar 30 '15 at 23:24
  • that did not work. let me rephrase my question. I want to get the result for different dates in different columns. Each result in its respective date column – Ali Shaikh Mar 30 '15 at 23:31
  • @Ali Shaikh : search for pivoting. – a1ex07 Mar 31 '15 at 14:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.