How to join 2 tables and show all first table even second table where clause not met?

Question

I want to make simple school attendance/presence, the idea is teacher should decide presence status of student every day.

I have 2 table, student and presence

Here is student table

Here is presence table

Now I want to make interface in my website which show all student by class_name, and date_presence.

If all student in class and current date doesn't have presence status, then it show all student with presence status "Not yet decide"

If some student in class and current date doesn't have presence status, then it show student presence status A/I/S for student which already have presence status set, and the other student as "Not Yet Decide"

So here is my query

SELECT `a`.`student_id`, `a`.`student_name`, `b`.`presence_id`, `b`.`presence_status`, `b`.`date_presence` 
FROM `student` `a` 
left JOIN `presence` `b` ON `a`.`student_id` = `b`.`student_id` 
WHERE `a`.`class_name` = 'KLJ0009' and `b`.`date_presence` = '2018-07-24'

and here is the result

It show only 3 people, what I want is show all the student which class_name is KLJ0009 and if student doesn't have presence_status just show presence_id, presence_status, and date_presence as null

How I can do that?

Thanks in advance.

Answer 1

You need to move the following condition to the ON part of your query:

`b`.`date_presence` = '2018-07-24'

So your query looks like the following:

SELECT `a`.`student_id`, `a`.`student_name`, `b`.`presence_id`, `b`.`presence_status`, `b`.`date_presence` 
FROM `student` `a` LEFT JOIN `presence` `b` ON `a`.`student_id` = `b`.`student_id` AND `b`.`date_presence` = '2018-07-24'
WHERE `a`.`class_name` = 'KLJ0009'

Note: In case you want to query always for the current date you can use NOW or CURDATE instead of 2018-07-24.

demo: https://www.db-fiddle.com/f/762aXDfQConnR8XbwYQ3z4/0

Answer 2

You need current date ,so use now() function and case when for status

SELECT `a`.`student_id`, `a`.`student_name`, `b`.`presence_id`,
case when `b`.`presence_status` is not null then `b`.`presence_status` else 'Yet not Decided' as  presence_status, `b`.`date_presence` FROM `student` 
`a` left JOIN `presence` `b` 
 ON `a`.`student_id` = `b`.`student_id` 
 and  `b`.`date_presence` = date(now())
 WHERE `a`.`class_name` = 'KLJ0009'