Reputation: 13
I have two tables.
tbl1
looks like this:
CODE Role Sequence
0001 Role1 1
0002 Role2 2
0003 Role3 3
0004 Role4 4
0005 Role5 5
tbl2
looks like this:
Code RoleCode User DateFrom DateTo
00002 0001 Usr1 2019-11-09 9998-12-31
00002 0002 Usr2 2019-11-09 9998-12-31
I want to join them like this which is I have already made:
tbl1.Code tbl1.Role tbl2.User tbl2.DateFrom tbl2.DateTo
0001 Role1 Usr1 2019-11-09 9998-12-31
0002 Role2 User2 2019-11-09 9998-12-31
0003 Role3 NULL NULL NULL
0004 Role4 NULL NULL NULL
0005 Role5 NULL NULL NULL
By doing this query:
SELECT tbl1.Code,
tbl1.Role,
tbl2.User,
tbl2.DateFrom,
tbl2.DateTo
FROM tbl1
LEFT JOIN tbl2
ON tbl1.Code = tbl2.RoleCode
Question: How can I show all rows of tbl1
when I put a where clause in my query like this:
SELECT tbl1.Code,
tbl1.Role,
tbl2.User,
tbl2.DateFrom,
tbl2.DateTo
FROM tbl1
LEFT JOIN tbl2
ON tbl1.Code = tbl2.RoleCode
WHERE tbl2.Code = '00002'
Upvotes: 0
Views: 36
Reputation: 222402
Putting a condition on a left join
ed table in the where
clause implicitly turns it to an inner join
, and hence eliminates all records for which the left join
did not match from the resultset.
You need to put the condition on the left join
ed table in the on
clause of the join
:
SELECT tbl1.Code,
tbl1.Role,
tbl2.User,
tbl2.DateFrom,
tbl2.DateTo
FROM tbl1
LEFT JOIN tbl2
ON tbl1.Code = tbl2.RoleCode AND tbl2.Code = '00002'
Upvotes: 1