CODEWITHSUNDEEP
sqlsql-servercase
user3088720
user3088720

Reputation: 21

CASE statement in WHERE clause with != condition

I'm trying to figure out how to use a case statement in a where clause (with !=), here is an idea of what I am trying to do:

SELECT *
FROM Table1 JOIN Table2 JOIN Table3
WHERE 
  CASE @fruit
    WHEN 'Apples' THEN (What would go here to return only data related to apples in Table1?)
    WHEN 'Citrus' THEN (What would go here to return data related to either oranges or lemons in Table2?)
    WHEN 'Other' THEN (return all data not related to apples, oranges, or lemons from all 3 tables) (!=)
  END

I've seen a few examples of a case statement in a where clause, but not one with a != condition. Any ideas?

Upvotes: 1

Views: 135

Answers (3)

asmgx
asmgx

Reputation: 8034

just use ELSE for everything else

SELECT *
FROM Table
WHERE 
  CASE @fruit
    WHEN 'Apples' THEN (What would go here to return only data related to apples?)
    WHEN 'Oranges' THEN (What would go here to return only data related to oranges?)
    ELSE (return all data not related to apples or oranges) (!=)
  END

OR you can use the parameter inside the case, like this

SELECT *
FROM Table
WHERE 
  CASE 
    WHEN @fruit = 'Apples' THEN (What would go here to return only data related to apples?)
    WHEN @fruit = 'Oranges' THEN (What would go here to return only data related to oranges?)
    WHEN @fruit <> 'Apples'  AND @fruit <> 'Oranges'  THEN (return all data not related to apples or oranges) (!=)
  END

Upvotes: 0

danielsepulvedab
danielsepulvedab

Reputation: 674

If I understood your question, you want to evaluate other boolean expression besides equality. The CASE statement has two forms, the one that you're trying (always look for equality) and this other form:

CASE
  WHEN <boolean expression> THEN <result expression>
  WHEN <boolean expression> THEN <result expression>
  ELSE <else expression>
END

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

A simple AND/OR combination will do:

SELECT *
FROM Table
WHERE 
    (@fruit != 'Other' AND Fruit = @fruit) OR
    (@fruit = 'Other' AND Fruit NOT IN('Apples', 'Oranges')

Upvotes: 2

Related Questions