CODEWITHSUNDEEP
mysql
Patrick_W
Patrick_W

Reputation: 3

mySQL Check if all link items are a specified value

I want to check if all linked Items that are a specified value. I am not sure how to properly explain it but the example is pretty self-explanatory in my opinion.

Example Database (simplified)

Table Groups
   PK_Groups  int
   Groupname  varchar

Table Person
   PK_Person  int
   Name       varchar
   isAdult    tinyint(1)
   FK_Groups  int

How can I check in which groups are only adults (1 query that returns all groups)? Is there a way to do it in SQL or do I have to do "manually"?

Thanks in advance

SELECT PK_Groups 
FROM Groups 
INNER JOIN Person on PK_Group = FK_Groups 
WHERE isAdult = 0

WHERE doesn´t work because there are some that are adult

and grouping by name and checking if adult doesnt work either because its inside the group

Couting non adults for each group would work (If Count <=0) but only for each group and not all groups at once

Upvotes: 0

Views: 31

Answers (1)

Barmar
Barmar

Reputation: 781096

Check the minimum value of isAdult for each group. If they're all adults, the minimum value is 1.

SELECT PK_Groups 
FROM Groups 
INNER JOIN Person on PK_Groups = FK_Groups 
GROUP BY PK_Groups
HAVING MIN(isAdult) = 1

Note that this isn't the general way to test that all values in a set are a specific value. It only works when the value you're trying to check for is the highest possible value (you can also test for the lowest value by changing MIN() to MAX()).

The more general condition can be tested with:

HAVING SUM(column = value) = COUNT(*)

Upvotes: 2

Related Questions