CODEWITHSUNDEEP
sqlsql-servercase
user2675939
user2675939

Reputation: 371

CASE/EXISTS IN WHERE Clause in SQL Server

I have a query that I need to add condition in WHERE Clause. The condition is if companyID is within the certain list then only show users that has LocationTypeID else show all users. Is there anyway to achieve this?

SELECT 
    UserID
FROM [Account].[User] AS [User]
INNER JOIN [Account].[Location] as [Location]
    ON [Location].[LocationID] = [User].[LocationID]

I am trying to add condition in where clause, but not sure how to do it.

WHERE
CASE WHEN Location.CompanyID IN (123, 344, 444, 565)
THEN 'AND Location.LocationTypeID IS NOT NULL'

Upvotes: 0

Views: 132

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use you condition like below

SELECT 
    UserID
FROM [Account].[User] AS [User]
INNER JOIN [Account].[Location] as [Location]
    ON [Location].[LocationID] = [User].[LocationID] 
    where        
    (
     Location.CompanyID IN (123, 344, 444, 565)
     AND Location.LocationTypeID IS NOT NULL
    ) 
     OR 
     (
      Location.CompanyID not IN (123, 344, 444, 565)          
     )

Upvotes: 2

IShubh
IShubh

Reputation: 364

Try the following query -:

SELECT 
    UserID
FROM [Account].[User] AS [User]
INNER JOIN [Account].[Location] as [Location]
    ON [Location].[LocationID] = [User].[LocationID]
WHERE
Location.CompanyID IN (123, 344, 444, 565)
AND Location.LocationTypeID IS NOT NULL

SQL server 2014

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

People always seem to want to use CASE when simple logical combinations will work:

WHERE
Location.CompanyID NOT IN (123, 344, 444, 565)
OR Location.LocationTypeID IS NOT NULL

Note that I've inverted the first condition so we only need the second condition to be true if the first turns out to be false. Of course, they may both be true but that shouldn't matter.

Upvotes: 2

Related Questions