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!

So you want to retrieve the animal which has entries for all names in the `IN` clause? — Jan S, Nov 28 '11 at 05:57
I believe the OP does not want an animal if it has a name outside those specific names, not necessarily every one of those names. — Adam Wenger, Nov 28 '11 at 05:58

Brandon Moore · Answer 1 · 2011-11-28T06:40:32.813

10

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.

edited Nov 28 '11 at 06:40

answered Nov 28 '11 at 06:33

Brandon Moore

8,590
15
65
120

Yeah, better than my solution, pretty sure. Didn't think to `HAVING animal NOT IN` - nice & +1 – Adam Wenger Nov 28 '11 at 06:41
@SashiKant For this data, mine will also only return one row. I (we) believe that is the request of the OP – Adam Wenger Nov 28 '11 at 06:49
The requirement is this :: "And I want to pick all the animals where ALL his group members have name IN('homer','bart','marge','lisa','maggie')" look at the phrase - All the animals – Sashi Kant Nov 28 '11 at 06:55
@SashiKant Maybe my english didnt helped me to express what I want... The query its doing the job I need! In this case, yes, it will return a single row, so, – Nazareno Lorenzo Nov 28 '11 at 07:00
Generally, I think, the query should be more efficient if the results were filtered *before* grouping, i.e. in the WHERE clause (using the same condition). – Andriy M Nov 28 '11 at 07:40

score 8 · Accepted Answer · edited May 23 '17 at 12:34

Put another way, it's ‘Select all the animals for whom the number of corresponding names not matching the condition IN ('homer','bart','marge','lisa','maggie') is 0’.

So it could be implemented also like this:

SELECT animal
FROM pets
GROUP BY animal
HAVING COUNT(name NOT IN ('homer','bart','marge','lisa','maggie') OR NULL) = 0
  /* or: SUM(name NOT IN ('homer','bart','marge','lisa','maggie')) = 0 */

This expression

name NOT IN ('homer','bart','marge','lisa','maggie') OR NULL

results in either 1 or NULL, consequently causing COUNT either to count the corresponding occurrence of name or to omit it. (As to why OR NULL is there and how the entire expression works, I'll refer you to this question: Why do I need "OR NULL" in MySQL when counting rows with a condition.)

As you can see, you could alternatively use SUM to do the same, and the expression for SUM is apparently shorter, as the OR NULL part isn't needed there. I usually prefer COUNT() to SUM() when I need to count things, as opposed to adding up arbitrary values. But, to my knowledge, there's no advantage of one before the other, so which function to use is up to your personal preferences/tastes.

Nice and +1. I wonder how you'd write that for Sql Server though, because any bitwise operation with null in Sql Server will result in null. And I'd be interested to know the performance difference between my query and yours in MySql if you happen to have the time to check (I don't have MySql or I'd check myself). — Brandon Moore, Nov 28 '11 at 09:41
I don't have MySQL either, can't say about the performance, sorry. In SQL Server I typically use `CASE` expressions for conditional counting, so it could be like this: `HAVING COUNT(CASE WHEN name NOT IN ('homer','bart','marge','lisa','maggie') THEN 1 END) = 0`. The `1` bit here could be replaced with pretty much anything except `NULL`, because `COUNT` only *counts* values, no matter what values exactly as long as the condition is met. When it is *not* met, the implied `ELSE NULL` branch is evaluated and so `COUNT` counts nothing in that case. The `CASE` method should also work in MySQL, btw. — Andriy M, Nov 28 '11 at 11:16

Adam Wenger · Answer 3 · 2011-11-28T06:11:33.483

1

I do not think this will be very efficient if your query is large, but for small values, this should do the trick:

SELECT p.animal
FROM pets AS p
LEFT JOIN pets AS p2 ON p.name = p2.name
   AND p2.Name NOT IN ('homer','bart','marge','lisa','maggie')
WHERE p2.id IS NULL
GROUP BY p.animal
HAVING COUNT(p.Animal) =
(
   SELECT COUNT(1)
   FROM pets AS p3
   WHERE p3.animal = p.animal
)

I'll try to explain my logic so that others may be able to improve upon it:

I am using a LEFT JOIN into Pets AS p2 to see which animals have a restricted value (will have a record in p2)

If p2.Animal IS NULL, we are down to only records for the allowed names. I then check that the COUNT() for that animal matches the number of animals that are in the pets table for that animal (correlated sub query).

edited Nov 28 '11 at 06:11

answered Nov 28 '11 at 06:05

Adam Wenger

17,100
6
52
63

Does my answer look like what you were going for? – Brandon Moore Nov 28 '11 at 06:37
@BrandonMoore Yes, we both got the answer, but very different ways. On my very small test table, yours appears to be 22% more efficient. (according to SQL Server) Nice job. – Adam Wenger Nov 28 '11 at 06:43
+1 for figuring out the word problem first. I didn't understand quite what he was looking for until I looked at your answer :) – Brandon Moore Nov 28 '11 at 06:44

score 1 · Answer 4 · answered Nov 28 '11 at 08:33

1

SELECT DISTINCT animal
FROM pets p
WHERE NOT EXISTS
      ( SELECT *
        FROM pets pg
        WHERE pg.animal = p.animal
          AND pg.name NOT IN ('homer','bart','marge','lisa','maggie')
      )

answered Nov 28 '11 at 08:33

ypercubeᵀᴹ

113,259
19
174
235

Select grouping where all the elements meet the condition

4 Answers4