CODEWITHSUNDEEP
mysqlselect
Simon M.
Simon M.

Reputation: 2482

SQL : Select row where a column of this row does't match with a certain value

I have a table with for exemple, a column id_type and an other column num_area. And I want to search all the id_type where the value of num_area doesn't match with a certain value.

id_type    num_area 
----        ----   
1           121
2           121
1            95
3            47
4            65

For exemple, if I want id_type that doesn't have the num_area 121, it will returns me, id_type 3 and 4.

Thanks

Upvotes: 0

Views: 41

Answers (2)

Arun Krish
Arun Krish

Reputation: 2153

Try this query. I got the result without subquery.

SELECT DISTINCT(e1.id_type) 
 FROM example e1
 JOIN example e2 ON(e1.id_type = e2.id_type AND e2.num_area != '121');

Upvotes: 0

amdixon
amdixon

Reputation: 3833

plan

  • list id_type where num_area is 121
  • list distinct id_type not in above

query

select distinct id_type
from example
where id_type not in
(
  select id_type
  from example
  where num_area = 121
)
;

output

+---------+
| id_type |
+---------+
|       3 |
|       4 |
+---------+

sqlfiddle

Upvotes: 3

Related Questions