Display a record although the condition return zero record

I have MySql query like this:

SELECT name, id_number, hr.id, file, created_at, updated_at 
From users u
LEFT JOIN homework_targets ht on u.class_id = ht.class_id
LEFT JOIN homework_replies hr on u.id = hr.user_id
WHERE u.role = 'student' AND hr.homework_id = 8                

This query work just fine, but not like what I expected. I want to display all the student (users) record is displayed although they don’t have a record on homework_replies that match the homework_targets is it possible? if it possible how can I accomplish it? thanks.

Answer

Move the hr.homework_id condition from WHERE to ON to get true LEFT JOIN result:

SELECT name, id_number, hr.id, file, created_at, updated_at 
From users u
LEFT JOIN homework_targets ht on u.class_id = ht.class_id
LEFT JOIN homework_replies hr on u.id = hr.user_id AND hr.homework_id = 8   
WHERE u.role = 'student'

(If you have it in the WHERE clause, you’ll get regular INNER JOIN result.)