Choose a grouping in which all elements satisfy the condition

Question

I am having problems with MySQL Query.

I want to select all the elements from the table, grouping them from a specific column, but only retrieve those where ALL the elements in the group match the WHERE clause.

For example, I have a table called "pets" with three 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 select all animals where ALL of his group members have the name IN ('homer', 'bart', 'marge', 'lisa', 'maggie')

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

Answer 1

In other words, "Select all animals for which the number of matching names that do not meet the IN ('homer','bart','marge','lisa','maggie') condition IN ('homer','bart','marge','lisa','maggie') is 0 .

Thus, it can also be implemented as follows:

 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 

leads to either 1 or NULL , which leads to the fact that COUNT should be considered the corresponding occurrence of name or omit it. (As for why OR NULL is and how the whole expression works, I will refer to this question: Why do I need "OR NULL" in MySQL when counting rows with a condition .)

As you can see, as an alternative, you can use SUM to do the same, and the expression for SUM seems to be shorter since OR NULL not needed there. I usually prefer COUNT() to SUM() when I need to count things, rather than add arbitrary values. But, as far as I know, there are no advantages over each other, so which function to use depends on your personal preferences / tastes.

Answer 2

Take a picture. Let me know if I'm missing exactly what you are 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 ) 

This 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 need and then just exclude them.

Answer 3

I don't think it will be very effective if your request 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 will try to explain my logic so that others can improve it:

I use LEFT JOIN in Pets AS p2 to see which animals have limited value (will have an entry in p2)

If p2.Animal IS NULL , we only write entries for resolved names. Then I check that the COUNT() for this animal matches the number of animals that are in the pets table for that animal (correlated subquery).

Answer 4

 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') )