Ali Ali - 3 years ago 149
MySQL Question

all rows from the first table joined with the second table

I have this table

exam


ID | Name | Date
_____________________
1 | E1 | 2017-1-1
1 | E2 | 2017-1-2
1 | E3 | 2017-1-3


and this table
enroll


ID | U_ID | E_ID | Status
_________________________
1 | 1 | 1 | 0
1 | 1 | 2 | 1
1 | 2 | 1 | 1


the results I want are

Name | U_ID | Date | Status
_______________________________
E1 | 1 | 2017-1-1 | 0
E2 | 1 | 2017-1-2 | 1
E3 | 1 | 2017-1-3 | NULL


I tried this :

SELECT Name , U_ID , Date , Status
FROM exam LEFT JOIN enroll ON exam.ID = enroll.E_ID
WHERE U_ID = 1


the result I got this

Name | U_ID | Date | Status
_______________________________
E1 | 1 | 2017-1-1 | 0
E2 | 1 | 2017-1-2 | 1


Also tried this :

SELECT Name , U_ID , Date , Status
FROM exam LEFT OUTER JOIN enroll ON exam.ID = enroll.E_ID
WHERE U_ID = 1


but the same results.

what is wrong ? how can I get the desired results ?

Answer Source

Move the filter to ON condition

SELECT Name , U_ID , Date , Status 
FROM exam e
LEFT JOIN enroll en
       ON e.ID = en.E_ID
      AND en.U_ID = 1

When you use the filter where clause, for the non matching records U_ID will be NULL so it will filtered in result. When you use the filer in ON condition it says what are the records to be joined instead of filtering.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download