CODEWITHSUNDEEP
mysqlleft-join
Ruben_PH
Ruben_PH

Reputation: 1812

How to return null values using SQL Left Join

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.

Upvotes: 6

Views: 51203

Answers (4)

Gabriel
Gabriel

Reputation: 41

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;

Upvotes: 2

davidp_1978
davidp_1978

Reputation: 141

I believe putting the student filter into the JOIN predicate also gives the desired results. It works in the SQL Fiddle but I don't know how it affects the query plan.

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
**AND** b.student_id_no='05-0531';

Upvotes: 1

purgatory101
purgatory101

Reputation: 6770

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

Upvotes: 5

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

try this query ::

SELECT a.subject_id, b.grade, b.status
FROM tbl_curriculum a
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'
union
select subject_id,null,null 
from tbl_curriculum
where concat(subject_id,course_id) not in (
  select  concat(subject_id,course_id)
from tbl_enrolled_subjects
 WHERE student_id_no='05-0531')

Upvotes: 1

Related Questions