WHERE clause with nested multiple conditions
40,330
Solution 1
SELECT * FROM testing
WHERE
Age > case Location When 'Bhuj' then 20
When 'Mumbai' then 25
end
Solution 2
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.
![Himanshu](https://i.stack.imgur.com/ExSpa.jpg?s=256&g=1)
Author by
Himanshu
#SOreadyToHelp A passionate cricketer (by heart!). An enthusiastic programmer (by mistake!).
Updated on October 21, 2020Comments
-
Himanshu over 3 years
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?