CODEWITHSUNDEEP
mysqlsql
Cara Simpson
Cara Simpson

Reputation: 1

How can I select all non-unique field1s where field2 is never x?

Given the dataset below:

field1 field2
a      1
a      2
a      3
b      1
b      2
b      4
c      2
c      2
c      3

How can I determine which values for field1 are never associated anywhere in the table with a field2 value of 4?

The result would be

field1
a
c

SELECT field1 FROM table WHERE field2 <> 4 will include b because it appears in the table multiple times with other field2 values - how do I prevent this?

Upvotes: 0

Views: 49

Answers (2)

forpas
forpas

Reputation: 164099

You can group by field1 and put the condition in the having clause:

select field1
from tablename
group by field1
having sum(field2 = 4) = 0

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use not exists

select t1.* from table t1
where not exists( select 1 from table t2 where t1.field1=t2.field1 
                 and t2.field2=4)

or use not in

select * from table t1
where t1.field1 not in ( select field1 from table where field2=4)

Upvotes: 2

Related Questions