Group by having at least one of each item

Question

Say I have a table matching person_ids to pets. I have a list of the ideal pets that a homeowner must have (at least one of each) and, from the following table, I want to see who meets the requirements.

That list is, of course, (dog, cat, tiger). People can definitely have more than one of each, but these are essential (therefore person_id = 1 is the only one that works).

+---------+-----------+--------+
| home_id | person_id |  pet   |
+---------+-----------+--------+
|       1 |         1 | dog    |
|       2 |         1 | dog    |
|       3 |         1 | cat    |
|       4 |         1 | tiger  |
|       5 |         2 | dog    |
|       6 |         2 | cat    |
|       7 |         3 | <null> |
|       8 |         4 | tiger  |
|       9 |         4 | tiger  |
|      10 |         4 | tiger  |
+---------+-----------+--------+

I've been able to check who has a tiger or a cat by running:

select person_id, pet 
from house 
group by person_id having pet in ('dog','cat','tiger'), 

but obviously this gives the person_ids that have at least one of those pets – not all of them.

Answer 1

One way of doing this is to count how many different pets each person has and to compare it (i.e. join it) with the total number of different pets:

SELECT person_id
FROM   (SELECT   person_id, COUNT(DISTINCT pet) AS dp
        FROM     pets
        GROUP BY person_id) a
JOIN   (SELECT COUNT(DISTINCT pet) AS dp FROM pets) b ON a.dp = b.dp

EDIT:
If just some pets are considered "ideal", and this list is known upfront, the query can be greatly simplified by introducing this information in a where clause:

SELECT   person_id
FROM     pets
WHERE    pet IN ('dog', 'cat', 'tiger')
GROUP BY person_id
HAVING   COUNT(DISTINCT pet) = 3