CODEWITHSUNDEEP
sqlsql-server
William
William

Reputation: 6610

Having two parameters within a CASE statement within a WHERE clause

I'm running a query to produce all of the records where various parameters apply.

Say the table concerned holds details about people.

I want to produce the records for both 'Male' and 'Female' entries but I only want to show the men with an age lower than say, '40'.

Currently I have a query like this;

SELECT *
FROM table
WHERE gender IN('Male', 'Female') AND age < 40

How do I apply the age rule to the men only? With a CASE statement?

EDIT

As I have many other parameters within the WHERE clause which I didn't mention here, I want to avoid the use of the OR operator, apologies for not stating this to start with.

Upvotes: 2

Views: 289

Answers (5)

Taryn
Taryn

Reputation: 247650

there is no need for a CASE statement just alter your WHERE clause to include one filter specific for 'Male' records and one line for 'Female' records:

SELECT *
FROM table
WHERE (gender = 'Male' AND age < 40)
    OR gender = 'Female'

Here is a SQL Fiddle with a Demo

Edit if you have more items in your WHERE clause, you would just use parentheses to add more criterion:

SELECT *
FROM table
WHERE 
(
    (gender = 'Male' AND age < 40)
        OR gender = 'Female'
)
AND -- place your other WHERE filters here

Upvotes: 3

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

SELECT * 
FROM table 
WHERE (gender = 'Female' OR (gender = 'Male' AND age < 40)) AND ...
    -- the rest of your WHERE criteria goes here

Upvotes: 2

FabianoLothor
FabianoLothor

Reputation: 2967

Use IN no =

SELECT *
FROM table 
WHERE gender IN('Female') OR (gender IN('Male') AND age < 40);

Upvotes: 0

juergen d
juergen d

Reputation: 204746

SELECT *
FROM table
WHERE (age < 40 and gender = 'Male')
or gender = 'Female'

Upvotes: 1

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171371

There are many ways to write this, but this is probably the clearest:

SELECT * 
FROM table 
WHERE gender = 'Female' 
    or (gender = 'Male' AND age < 40)

Upvotes: 2

Related Questions