CODEWITHSUNDEEP
sqlsql-servert-sqlleft-joincase
Maksim Khaitovich
Maksim Khaitovich

Reputation: 4792

TSQL - using case switch to select variable from different left joins

I am trying to do join 2 tables together. The issue is that there are several ways to do it, from best join logic to the worse join logic. I use left join with same table a bunch of times, using different join condition each time and then I want to use a case switch to select variable value from best match. Example to explain it better is below:

select s.Product, 
    (case when c1.ID is not null then c1.ID
    case when c2.ID is not null then c2.ID
    case when c3.ID is not null then c3.ID
    case when c4.ID is not null then c4.ID
    else NULL) as ID
from
    dbo.Table1 as s 
    left join [dbo].Table2 as c1 on %some join logic%
    left join [dbo].Table2 as c2 on %some join logic%
    left join [dbo].Table2 as c3 on %some join logic%
    left join [dbo].Table2 as c4 on %some join logic%
where
    (
        c1.SKU is not null
        or c2.sku is not null
        or c3.sku is not null
        or c4.sku is not null
    )

The issue is that this stuff is not working - I just get a error 'Incorrect syntax near the keyword 'case'.' at line 3 (second case switch). Any advice how to tackle this?

Upvotes: 0

Views: 290

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

You don't even need a CASE expression to handle your select logic. Instead, you can just use the COALESCE() function to choose the first ID which is not NULL, in the order you want:

select
    s.Product,
    coalesce(c1.ID, c2.ID, c3.ID, c4.ID) AS ID
from dbo.Table1 as s
...

If you wanted to use your original CASE expression, then we can correct it by not repeating the CASE keyword for each condition:

select
    s.Product,
    case when c1.ID is not null then c1.ID
         when c2.ID is not null then c2.ID
         when c3.ID is not null then c3.ID
         when c4.ID is not null then c4.ID end as ID
from dbo.Table1 as s
...

Upvotes: 1

Related Questions