CODEWITHSUNDEEP
sqljoin
William
William

Reputation: 1527

Select field from table a if it exists, if it doesn't select field from table b

SELECT a.amount, CASE When d.name <> NULL Then d.name Else c.name End As 'name'
from a
JOIN b on a.id= b.id
LEFT JOIN c on a.tokenId = c.tokenId 
LEFT JOIN d on a.tokenId  = d.tokenId

I'm attempting to select the name from table d if it exists, and if it doesn't, select it from table c. The name field comes back as NULL, however I know they are not null in either of the tables.

Can anyone help me get this solved?

Upvotes: 0

Views: 136

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85046

Use COALESCE:

SELECT a.amount, COALESCE(d.name,c.name) "name"
from a
JOIN b on a.id= b.id
LEFT JOIN c on a.tokenId = c.tokenId 
LEFT JOIN d on a.tokenId  = d.tokenId

Upvotes: 1

Guffa
Guffa

Reputation: 700252

You can't use the <> operator to compare with null. Use the is operator:

SELECT a.amount, CASE When d.name is not null Then d.name Else c.name End As 'name'

You can also use the coalesce function:

SELECT a.amount, coalesce(d.name, c.name) As 'name'

Upvotes: 3

Related Questions