CODEWITHSUNDEEP
sqljoinconditional-statementsexists
Niclas
Niclas

Reputation: 13

SQL Join condition - not existing values

I have two tables that I want to join on two parameters. The first parameter (name) needs to be the same in both tables. When the second parameter (attribute) exists for the name in Table B, then I want the attributes to match, too. If the attribute from Table A doesn't exist in Table B, I want to join on a fix value in Table B, in this case black.

Example

I tried it like this:

LEFT OUTER JOIN on TableA.name = TableB.name and TableB.attribute = CASE WHEN NOT EXISTS(
     SELECT TableB2.attribute FROM TableB TableB2 WHERE TableB2.attribute = TableA.attribute) 
THEN 'black' ELSE TableA.attribute END

This worked but it is very slow and I'm sure there are better solutions, as I am new to SQL.

Thanks in advance :)

Upvotes: 0

Views: 1073

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

This type of defaulting is often handled using two left joins:

select . . .,
       colaesce(b.value, bdef.value) as value
from tableA a left join
     tableB b
     on b.attribute = a.attribute left join
     tableB bdef
     on bdef.attribute = 'black'

Upvotes: 1

Related Questions