CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005
Chidi Okeh
Chidi Okeh

Reputation: 1557

My attempt at LEFT JOIN isn't producing desired result. Any ideas?

I am seriously suffering from brain dead as I have done this successfully several times in the past.

This time, it isn't working.

I have 2 tables, tableA and tableB

TableA has all the surmons records

TableB has some but not all surmons.

The common key between them is surmonId.

The requirement is to display the surmons from tableB where there is a match between tableA and tableB but at the same time, display ALL the surmons from tableA.

In other words, give me from tableB any records that exist and all the records on tableA.

The lef join query below is only giving me records that exist in tableB.

Select distinct l.surmons from tableB b left join tableA a on b.surmonId = a.surmonId.

There are only 10 surmons on tableB and that's all I am getting.

Where am I messing up?

Thanks a lot in advance

Upvotes: 0

Views: 61

Answers (2)

Hart CO
Hart CO

Reputation: 34774

Either switch order of your tables:

SELECT DISTINCT a.surmons 
FROM tableA a 
LEFT JOIN tableB b 
   ON a.surmonId = b.surmonId

Or use my favorite, the RIGHT JOIN:

SELECT DISTINCT a.surmons 
FROM tableB b 
RIGHT JOIN tableA a 
   ON b.surmonId = a.surmonId

Upvotes: 3

dcp
dcp

Reputation: 55434

If you want everything from tableA, you need to make the left join from tableA to tableB.

Select distinct a.surmons from tableA a left join tableB b on a.surmonId = b.surmonId

Upvotes: 1

Related Questions