How to return null values using SQL Left Join

left-join, mysql

Solution

The reason you are not finding any nulls is because your where-clause is searching for rows with a student_id_no filled out. But, with the data you have the student_id_no will also be null in the case where the student has not taken the class...thus you are filtering out those.

Try this:

SELECT a.subject_id, b.grade, b.status, b.student_id_no
FROM tbl_curriculum a
LEFT JOIN tbl_enrolled_subjects b
ON a.course_id = b.course_id  AND a.subject_id = b.subject_id
where student_id_no is null or student_id_no = '05-0531'
order by subject_id

http://sqlfiddle.com/#!2/4c7b2/43

Problem

Please look at this SQL Fiddle for the tables and what query I have tried: SQL Fiddle So basically, I have two tables namely tbl_curriculum and tbl_enrolled_subjects. tbl_curriculum contains all the subjects (subject_id) a student should take based on his course (course_id). tbl_enrolled_subjects contains all the subjects the student has taken/enrolled based on tbl_curriculum. I want to check which subjects the student has taken and which is not, the query should return something like this: ``` Subject_id|Grade|Status 23 | 2 |Passed 24 | 2 |Passed 31 | 2 |Passed 50 | 2 |Passed 83 | 1 |Passed 27 |NULL |NULL 28 |NULL |NULL 29 |NULL |NULL ``` . . . And So On. Subject_ID with Grade and Status mean the student has already taken the subject. On the otherhand, NULL values indicates the the student has not taken those subjects yet. I used this query: ``` SELECT a.subject_id, b.grade, b.status FROM tbl_curriculum a LEFT JOIN tbl_enrolled_subjects b ON a.course_id = b.course_id AND a.subject_id = b.subject_id WHERE b.student_id_no='05-0531'; ``` But I keep getting only the subjects the student has taken. ``` Subject_id|Grade|Status 23 | 2 |Passed 24 | 2 |Passed 31 | 2 |Passed 50 | 2 |Passed 83 | 1 |Passed ``` Am I missing something? Thanks in advance.

Original source