I am making a query but it is not working properly. I have two tables one called subarea and the other student_subarea. In the subarea table I have the id and the fields of study and in the student_subarea the student choices, composed by the id (PK), the student_id and the student_subarea which is a FK to subarea which links the subarea id and the student_subarea.
What I want to acomplish is to obtain all fields of study in one column and in another column the id of the student if he is in the class. Otherwise, show null or something.
SELECT a.`id` , a.`name` , a.`area_id` , u. *
FROM `subarea` a
LEFT JOIN student_subarea u ON u.subarea_id = a.id
WHERE u.student_id =50
OR u.student_id IS NULL
The general rule for
left join and filtering is to put the filtering clauses in the
on clause for all but the first table. So this may do what you want:
SELECT a.`id` , a.`name` , a.`area_id` , u. * FROM `subarea` a LEFT JOIN student_subarea u ON u.subarea_id = a.id AND u.student_id = 50;
How do you remember this logic? A
left join returns all rows from the first table even when there is no match on the second table. That appears to be what you want.
The problem with your logic is that students other than student
50 match the logic. So, those rows get filtered out.