CODEWITHSUNDEEP
sqlsql-servert-sqlcase
JBone
JBone

Reputation: 3203

Multiple values in SQL CASE's THEN Statement

I am wondering if it is possible to specify multiple values in the then part of a case statement in T-SQL?

I have attached a chunk of code where I am using this to join in some tables in a query. I have included a comment in the snippet.

LEFT JOIN Business B ON v.BusID = B.BusID
LEFT JOIN BusinessTypeKey T ON B.BusinessTypeID = T.BusTypeID
LEFT JOIN Location L ON L.BusID = B.BusID
AND L.HeadQuarters = CASE 
WHEN (SELECT COUNT(1) from Location L2
WHERE L2.BusID = B.BusID) = 1                                                                           
THEN 1,0   -- Would like to specify either 1 or 0 here. I suppose I could also make it euqal to -> L.HeadQuarters but would like a better way to impose it                                                                                                  
ELSE 1  
END

Upvotes: 2

Views: 1173

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

This is a little ugly, but assuming HeadQuarters is not a decimal/numeric type and only integer values, 

AND L.HeadQuarters BETWEEN CASE WHEN (SELECT COUNT...) = 1 THEN 0 ELSE 1 END AND 1;

Upvotes: 4

Chains
Chains

Reputation: 13157

You mean...?

LEFT JOIN whatever
ON...
CASE...WHEN...THEN...END = 1
OR
CASE...WHEN...THEN...END = 0

Upvotes: 0

Related Questions