all rows from the first table joined with the second table

Question

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 ?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.

all rows from the first table joined with the second table

Sign up