CODEWITHSUNDEEP
sqlsql-server
Ratha
Ratha

Reputation: 9692

Return rows if they don't have entry in second table too?

I'm joining 2 tables(table A and table B), where I expect it to return matching results from tableB. also, if no matching results available return empty.

eg:

Select a.ID,a.Code,b.result 
from Table A as a
LEFT JOIN table B as b on a.ID=b.codeid
Where a.Timestamp<'2019-07-01' and b.xx=xx

Currently I get:

Sample data I get:

ID  Code Result
1   AS01 Pass
2   BXY  Fail
....

I prefer to get 

 ID   Code Result
 1    AS01 Pass
 2    BXY  Fail
 .... 
 1000 DCY

In above, if particular ID (eg: 1000) not have any entry in table B I'm not getting anything.

But I want to return empty Result column for that 1000 Id. How to do that?

Upvotes: 0

Views: 84

Answers (1)

juergen d
juergen d

Reputation: 204756

Your where condition filtering on tableB turns your left join into an inner join. Put that condition directly into the join:

SELECT a.ID, a.Code, b.result 
FROM TableA a
LEFT JOIN tableB b ON a.ID = b.codeid
                  AND B.xx = xx
WHERE a.Timestamp < '2019-07-01'

Upvotes: 2

Related Questions