pokerplayer23 pokerplayer23 - 4 months ago 9
SQL Question

SQL Server - Using Case in a Where IN section of a Join

I'm trying to use

CASE
for a
WHERE IN
clause of my code in a join. Is this not possible to do or is my syntax just bad? Any suggestions would be greatly appreciated. Thanks.

LEFT JOIN
MyTable ON MyTable.ID = SomeTable.ID
AND MyTable.ResultDate IN (CASE
WHEN metricid IN ('0040','0015')
THEN '('01312016','03312016','06302016')'
ELSE '('20160630')'
END)


I am using SQL Server 2014.

Answer

You can't but you can do something like this:

LEFT JOIN 
    MyTable ON MyTable.ID = SomeTable.ID 
            AND ((metricid IN ('0040','0015') AND MyTable.ResultDate IN ('01312016','03312016','06302016')) OR ((metricid NOT IN ('0040','0015') AND MyTable.ResultDate='20160630')))

It's ugly but it should work :)

Anoter approach would be to have to queries and do a UNION. So, the first one filters by metricid IN ('0040','0015') and does a LEFT JOIN to MyTable based on ResultDate IN ('01312016','03312016','06302016'). And the other one filters by metricid NOT IN.