CODEWITHSUNDEEP
mysqlsql
Hao Yang
Hao Yang

Reputation: 15

MySQL: Only selecting groups with only certain values

I would like to select groups that have only certain values in them. This would exclude groups that have those values and other values.

In details, I would like to select groups with a value in field, Type, of 1 and 2

For example, consider the following table

         Type1   f_Name     Product
User A     1       Bob       Orange
User A     2       Bob       Apple
User B     1       Bill      Orange
User B     2       Bill      Apple
User B     3       Bill      Peach

I would like, User A to show up and not User B in this case.

I have tried to do it with the NOT IN clause, but the data will just take out the values and include the user with the unwanted value. In this case, User B shows up but without the value 3. Please, any help would be appreciated.

Upvotes: 1

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271201

You can use group by and having to identify the groups:

select f_name
from t
group by f_name
having sum( (Type1 = 1) ) > 0 and
       sum( (Type1 = 2) ) > 0 and
       sum( (Type1 in (1, 2) ) = count(*);

Each condition in the having clause counts the number of rows that match the condition. The > 0 says that there is at least one row. The = count(*) says all rows match.

Upvotes: 1

Related Questions