Reputation: 1422
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.
Upvotes: 0
Views: 23
Reputation: 44776
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.)
Upvotes: 2