SQL Select Statement

Question

I am trying to select a set of data including female and male in 50 states. However, I need to exclude 5 states with both female and male, and one state with female. I'm not sure how to have two conditions in one where statement.

Below is the simple version of the code. The last row of the code is clearly wrong.

I want to say 'I want to exclude data when state code is OR, CA, WA, OH and VA', also 'I want to exclude data when state code is DC and gender is female'.

I hope you can understand what I am trying to ask. Any advice would be greatly appreciated. Thank you!

SELECT * FROM dataset WHERE state_code NOT IN ('OR','CA','WA','OH','VA) AND (state_code <> DC AND gender <> female)

Onluy add quote to you select literal SELECT * FROM dataset WHERE state_code NOT IN ('OR','CA','WA','OH','VA) AND (state_code <> 'DC' AND gender <> 'female') — ScaisEdge
– ScaisEdge, Commented Jun 6, 2016 at 17:08

Uueerdo · Accepted Answer · 2016-06-06 17:07:03Z

This is the most literal translation of your conditions I can think of:

SELECT * FROM dataset WHERE state_code NOT IN ('OR','CA','WA','OH','VA') AND NOT (state_code = DC AND gender = 'female') ;

It is logically equivalent to Remi's answer, as NOT (X AND Y) is the same as (NOT X OR NOT y).

Rémi Lepage · Accepted Answer · 2016-06-06 17:06:44Z

Here is the corrected query :

SELECT * FROM dataset WHERE state_code NOT IN ('OR','CA','WA','OH','VA') AND (state_code != 'DC' OR gender != 'female');

The WHERE state_code NOT IN ('OR','CA','WA','OH','VA')line excludes 5 state_code and the AND (state_code != 'DC' OR gender != 'female') line excludes women living in 'DC' state.

Marco · Accepted Answer · 2016-06-07 06:36:43Z

0

This shoult be correct:

SELECT * FROM dataset WHERE state_code NOT IN ('DC','OR','CA','WA','OH','VA') OR (state_code == 'DC' AND gender == male)

edited Jun 7, 2016 at 6:36

answered Jun 6, 2016 at 17:05

Marco

474 bronze badges

1 Comment

Uueerdo Over a year ago

That would not filter out "DC female" as they are not in the "NOT IN" list.

Collectives™ on Stack Overflow

SQL Select Statement

3 Answers 3

1 Comment

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Related