CODEWITHSUNDEEP
sql-servert-sqljoinnull
Johann
Johann

Reputation: 29867

T-SQL: Left Join and returning a null when no join is made

I have the following table, TableA, with some data

ColA  ColB
0      5
0      6

I have another table, TableB, with some data

ColC  ColD   ColE
5      10     5
5      15     10
6      20     10

ColC is the foreign key for ColB. For each row in TableA I need to return ColA, ColB and ColD. The row to select in TableB depends on the value of ColE that is set through a parameter.

For example: If my parameter is set to 5, I should get two rows back as follows:

ColA  ColB  ColD
0      5     10
0      6     null

However, if my parameter is anything other than 5 and no row exists in TableB, it uses a parameter value of 5 instead and retrieves the value from ColB (if a row exists).

I tried several things but can't seem to come up with the solution. Thank you for your help!

Upvotes: 0

Views: 1650

Answers (2)

paparazzo
paparazzo

Reputation: 45096

select a.ColA, a.ColB, COALESCE(b.ColD, b5.ColD)
  from TableA a
  left outer join TableB b 
  on a.ColB = b.ColC 
  and b.ColE = 6
  left outer join TableB b5 
  on a.ColB = b5.ColC 
  and b5.ColE = 5

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

Try this:

select ColA, ColB, ColD
  from TableA a
  left outer join TableB b on (a.ColB = b.ColC and b.ColE = 5)

Upvotes: 2

Related Questions