mysql select if and

Question

I'm building a query on this table

my_table
field1 | field2
1      | y
2      | y
3      | y
4      | n
1      | n
2      | n
3      | y
4      | y

I am looking for a rule (maybe IF) to select all the records excluding the ones where field1=1 AND field2=y

this record will not be selected:

field1 | field2
1      | y

while this is ok

field1 | field2
1      | N

EDIT:

I add this details: the query is longer:

SELECT * FROM my_table WHERE time>'time1' AND time<'time2' AND admin_area='0'

in this query i have to append a filter: if field2=Y exclude records where field1=1

MikeTheReader · Accepted Answer · 2011-05-27 02:25:22Z

3

How about:

SELECT * FROM my_table WHERE field1 <> 1 OR field2 <> 'y'

answered May 27, 2011 at 2:25

MikeTheReader

4,1903 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michele Over a year ago

what about if i have a long where and rules before?

Michele Over a year ago

i know the not operato but can you show me a query for this case?

MikeTheReader Over a year ago

You could wrap this part into a long WHERE clause, such as: WHERE blah blah blah AND (field1 <> 1 OR field2 <> 'y') AND blah blah blah...

Salman Arshad · Accepted Answer · 2011-05-28 04:19:42Z

2

Regarding:

in this query i have to append a filter: if field2=Y exclude records where field1=1

SELECT *
FROM my_table
WHERE time > 'time1'
AND   time < 'time2'
AND   admin_area = 0
AND ( (
    field2 = 'Y' AND field1 <> 1
) OR (
    field2 IS NULL OR field2 <> 'Y'
) )

edited May 28, 2011 at 4:19

answered May 27, 2011 at 9:41

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Sky Kelsey · Accepted Answer · 2011-05-27 02:29:43Z

0

Here is The MySQL SELECT syntax reference. You can also use != or <> for an inequality test.

answered May 27, 2011 at 2:29

Sky Kelsey

19.3k5 gold badges39 silver badges78 bronze badges

1 Comment

silverfox Over a year ago

SELECT * FROM my_table WHERE (field1, field2) <> (1, 'y');

Collectives™ on Stack Overflow

mysql select if and

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related