CODEWITHSUNDEEP
mysqlsql
solitaria
solitaria

Reputation: 131

How do you return only groups that contain a specific value in MySQL?

Given a table mytable with 2 columns like

|   foo   |  bar    | 
|   a     |   10    | 
|   b     |   10    |   
|   b     |   15    | 
|   c     |   10    | 
|   c     |   10    | 
|   c     |   10    |  
|   d     |   10    | 
|   d     |   45    |  
|   e     |   20    |

How I return the groups of foo that only contain the value 10?

The resulting dataset for the above should look like

|   foo   |  bar    | 
|   a     |   10    |  
|   c     |   10    |

Note that b and d do not get returned because 15 and 45 are also values in those groups.

Upvotes: 0

Views: 35

Answers (3)

Ken Lee
Ken Lee

Reputation: 8103

According to your requirement, you need to tell the system to

  1. retrieve the records where bar=10
  2. filter out those which contains bar <> 10

The following are the suggested codes:

select foo, bar from mytable 
where 
bar=10 and foo not in (select foo from mytable where bar <> 10)
group by foo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271201

You can use group by and having:

select foo
from mytable
group by foo
having min(bar) = max(bar) and min(bar) = 10;

if 10 is the minimum value:

having max(bar) = 10

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

You could use exists logic here:

SELECT DISTINCT foo
FROM yourTable t1
WHERE NOT EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.foo = t1.foo AND t2.bar <> 10);

This query should benefit from the following index:

CREATE INDEX idx ON yourTable (foo, bar);

Upvotes: 0

Related Questions