Mysql select where

Question

Mysql select where value 90 and 45 but not 90 with type 1 here is my sample table

value   |   type    |   status
90          1           0
90          1           1
90          0           0
90          0           1
45          1           0
45          1           1

My code didn't get the desired output

SELECT * FROM table WHERE type IN (1, 0) AND value != 90

Desired output is:

value   |   type    |   status
90          0           0
90          0           1
45          1           0
45          1           1

Answer 1

You're looking at a fairly basic compound condition - you want "all type=0" or "all type=1 except when value=90", which would be expressed as

WHERE (type = 0) OR (type = 1 AND value != 90)

(based on your own query), or

WHERE (value = 45) OR (VALUE = 90 AND type != 1)

(based on your first sentence.)

Answer 2

You could try something similar to this:

create table test (val int, type int, status int);
insert into test values 
(90          ,1          ,0),
(90          ,1           ,1),
(90          ,0           ,0),
(90          ,0           ,1),
(45          ,1           ,0),
(45          ,1           ,1);

select *
from test 
where (val = 90 and type != 1) or val = 45;

| val | type | status |
|-----|------|--------|
|  90 |    0 |      0 |
|  90 |    0 |      1 |
|  45 |    1 |      0 |
|  45 |    1 |      1 |

SQLFiddle example: http://sqlfiddle.com/#!9/052c0/1