Glenn
Glenn

Reputation: 13

MSSQL Show rows with NULL value in Query with Where Clause

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

Answers (1)

GMB
GMB

Reputation: 222402

Putting a condition on a left joined 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 joined 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

Related Questions