Select grouping where all the elements meet the condition

Question

I'm having some troubles with a MySQL Query.

I want to select all the elements from a table, grouping from a certain column, BUT only retrieve those where ALL the elements in the group matches the WHERE clause.

For example, I have a table called "pets" with 3 columns:

Id| animal | name
1 | dog    | marge
2 | dog    | homer
3 | cat    | marge
4 | cat    | lenny
5 | rabbit | homer
5 | rabbit | carl
6 | rabbit | marge

And I want to pick all the animals where ALL his group members have name IN('homer','bart','marge','lisa','maggie')

If you can't understand my question, let me know. Thanks!

Answer 1

Give this a shot. Let me know if I'm missing the mark on what exactly it is you're looking for.

select animal
from pets
group by animal
having animal not in
(
select animal
from pets
where name not in ('homer','bart','marge','lisa','maggie')
group by animal
)

It sounds obvious when you say it, but the trick to getting only the results you want is sometimes to first create a query that gets all the results you don't want and then just exclude them.