Red Devil Red Devil - 5 months ago 9
SQL Question

left join not giving correct output

I am having 2 table

tableA

Accountid
-----------
10
11
12


tableB

Accountid | Date |
--------------------------------------------
10 | 2016-02-02 |
11 | 2016-02-02 |
11 | 2016-02-02 |
15 | 2016-02-03 |


I am expecting the output like

Accountid | ID |
------------------------------------
10 | 10 |
11 | 11 |
12 | NULL |


I am running this query

select A.accountid,b.accountid as ID
from tableA as A
left join tableB as B on a.accountid=b.accountid
where b.date between '2016-02-02' and '2016-02-02'


but it is giving me the output as, I am not sure where am I going wrong

Accountid | ID |
-----------------------------------
10 | 10 |
11 | 11 |


I am using MSSQL database.

Answer

When any field of the right table of a left join is occurred in WHERE clause then this join will behave like INNER JOIN

To get expected result your query should be like this

select A.accountid,b.accountid as ID 
from tableA as A 
left join tableB as B on a.accountid=b.accountid and 
                         b.date between '2016-02-02' and '2016-02-02'