13

I want to retrieve data with conditions in WHERE clause.
Here is my table something look like:

Name   Location    Age
----------------------
AAA     Bhuj       24
BBB     Mumbai     22
CCC     Bhuj       18
DDD     Bhuj       27
EEE     Mumbai     26

My condition in WHERE clause is:
if location = 'Bhuj' then Age>20 else if location = 'Mumbai' then Age>25

I am trying this code to achieve this:

SELECT * FROM testing
WHERE 
CASE Location WHEN 'Bhuj' THEN Age>20
              WHEN 'Mumbai' THEN Age>25
END;

This code works fine for MySQL (see this SQLFiddle) but does not work for SQL Server (see this SQLFiddle) and giving the following error:

Incorrect syntax near '>'.: SELECT * FROM testing WHERE case Location When 'Bhuj' then Age>20 When 'Mumbai' then Age>25 end

Any suggestion?

hgulyan
  • 8,099
  • 8
  • 50
  • 75
Himanshu
  • 31,810
  • 31
  • 111
  • 133

2 Answers2

21

I think this is what you're trying to achieve

   SELECT * 
   FROM testing
   WHERE (Location = 'Bhuj' AND Age>20) 
        OR (Location = 'Mumbai' AND Age>25)

Check SQLFiddle

UPDATE:

Case statement returns a value, you can't have a condition inside it.

hgulyan
  • 8,099
  • 8
  • 50
  • 75
  • Yes correct. But can't we use `CASE` statement which I shown in example? – Himanshu Aug 30 '12 at 07:47
  • We can use `CASE` in `WHERE` clause. See another answer by podiluska. – Himanshu Aug 30 '12 at 07:55
  • I didn't say you can't use case in where clause. What I say, is that you can't have a condition inside case statement. podiluska's answer is correct if you care about using case statement. – hgulyan Aug 30 '12 at 07:57
9
SELECT * FROM testing 
WHERE  
Age > case Location When 'Bhuj' then 20 
              When 'Mumbai' then 25 
              end
podiluska
  • 50,950
  • 7
  • 98
  • 104