How to return null values using SQL Left Join

Question

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.

Answer 1

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

Answer 2

Is very simple, you use IS EMPTY

In your entity you have defined a inverse key, then you call your entity principal where inverse Key IS EMPTY.

SELECT a FROM tbl_curriculum a WHERE a.enrollers IS EMPTY;

Then I have a field curriculum defined like tbl_enrolled_subjects in the tbl_curriculum

/**
 *
 * @ORM\OneToMany(targetEntity="tbl_enrolled_subjects", mappedBy="id")
 */
private $enrollers;