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.

What is your PRIMARY KEY? – Strawberry Aug 20 '15 at 15:52 — Strawberry, Aug 20 '15 at 15:52

Mureinik · Accepted Answer · 2015-08-20T15:59:52.073

6

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

edited Aug 20 '15 at 15:59

answered Aug 20 '15 at 15:54

Mureinik

297,002
52
306
350

So the problem with this is that some pets e.g. `rabbits` don't work, and this would "pollute" the results. Thanks though! – aralar Aug 20 '15 at 15:56
@miguel5 For some reason I misunderstood the question as if there cannot be non-ideal pets in the table. See my edited answer with an improved query. – Mureinik Aug 20 '15 at 16:00
Thank you!! (and to be fair I hadn't said that that was a requirement) – aralar Aug 20 '15 at 16:02

score 2 · Answer 2 · answered Aug 20 '15 at 15:53

2

select person_id
from house 
where pet = 'dog'
intersect
select person_id
from house 
where pet = 'cat'
intersect
select person_id
from house 
where pet = 'tiger'

You can use intersect to get people who have all the 3 pets.

answered Aug 20 '15 at 15:53

Vamsi Prabhala

48,685
4
36
58

The issue with this is that the actual query requires a couple of joins, but thanks! – aralar Aug 20 '15 at 15:57

score 2 · Answer 3 · answered Aug 20 '15 at 15:55

You could do it by counting each pet individually, like this:

select
    person_id 
FROM MyTable
GROUP BY person_id
HAVING 3 = (
    MAX(CASE pet WHEN 'dog' THEN 1 ELSE 0 END)
+   MAX(CASE pet WHEN 'cat' THEN 1 ELSE 0 END)
+   MAX(CASE pet WHEN 'tiger ' THEN 1 ELSE 0 END)
)

Each MAX in a group would be either 1 when the person has the corresponding pet, or 0 when the person doesn't have that kind of pet. Groups with at least one of each would produce the sum of 3 in the filter of the HAVING clause.

Group by having at least one of each item

3 Answers3