CODEWITHSUNDEEP
sql-serverdatabaset-sqljoinleft-join
CodesDDecodes
CodesDDecodes

Reputation: 132

Join two tables and show null in result set if match not found between joined tables in SQL

My table structure looks like this:

Table A:

AID       AName
1         AAA
2         BBB

Table B:

BID       AID    
10         1      
10         2    
11         2

In table B, AID is foreign key to primary key of table A. BID 10 belongs to AID 1 and AID 2 and BID 11 belongs to AID 2 only and not AID 1. I need result like this:

Expected Result:

BID    AID    AName
10       1    AAA 
10       2    BBB
Null     1    AAA
11       2    BBB

Table A is base table. Since in table B, for BID 11, it does not have record for AID 1, so the new result set should return NUll. I am not able to accomplish it using joins. How can I achieve it?

Upvotes: 1

Views: 549

Answers (1)

forpas
forpas

Reputation: 164064

You need a CROSS join of TableA to the distinct BIDs of TableB to get all combinations of AID and BID and then a LEFT join to TableB:

SELECT b.BID, a.AID, a.AName
FROM TableA a CROSS JOIN (SELECT DISTINCT BID FROM TableB) t
LEFT JOIN TableB b ON b.BID = t.BID AND b.AID = a.AID
ORDER BY t.BID, a.AID

See the demo.

Upvotes: 2

Related Questions