SQL query group by and having all

Question

I have a table:

Parent Child Educated
'P1', 'C1', 'YES'
'P1', 'C2', 'YES'
'P1', 'C3', 'NO'
'P2', 'C11', 'YES'
'P2', 'C12', 'NO'
'P3', 'C21', 'YES'
'P3', 'C22', 'YES'
'P4', 'C31', 'NO'
'P4', 'C32', 'NO'

Now, I need to find all the parents who have all their children educated, i.e, Educated='YES'.

Like in above case parent 'P3'

Can anyone suggest a query to fetch this

Find those that don't have any children educated = 'no' – Strawberry Aug 02 '17 at 14:39 — Strawberry, Aug 02 '17 at 14:39

score 8 · Accepted Answer · answered Aug 02 '17 at 14:53

8

I would do this as:

select parent
from t
group by parent
having max(educated) = min(educated) and max(educated) = 'YES';

The logic is slightly more complicated if educated could be NULL.

Actually, if the value is just 'YES' or 'NO', you can do the simpler:

select parent
from t
group by parent
having min(educated) = 'YES';

answered Aug 02 '17 at 14:53

Gordon Linoff

1,242,037
58
646
786

1

the second part of the answer threw me off. It is not just a shortcut, but another case, beware! – KarmaEDV Jan 08 '20 at 12:53

score 5 · Answer 2 · answered Aug 02 '17 at 14:40

5

select parent, 
       sum(case when educated='YES' then 1 else 0 end) as sum_educated,
       count(*) as count_all
from t
group by parent
having count_all=sum_educated

answered Aug 02 '17 at 14:40

StanislavL

56,971
9
68
98

In MySQL you don't actually need the CASE. – Strawberry Aug 02 '17 at 14:49

score 1 · Answer 3 · answered Aug 02 '17 at 14:43

1

Finding Parents that have any record with 'No' and using NOT IN to remove those from the result:

SELECT parent
FROM table
WHERE parent NOT IN (SELECT parent FROM table WHERE Educated = 'No')
GROUP BY parent

answered Aug 02 '17 at 14:43

JNevill

46,980
4
38
63

1

Didn't your mum ever tell you "Do not use `IN` for lists any longer than what you'd be prepared to write by hand"? :) – Caius Jard Aug 02 '17 at 14:56

score -3 · Answer 4 · answered Aug 02 '17 at 14:42

-3

select * from [YourTable] where Educated = 'Yes'

If you only want Parent column to be displayed then select Parent from [YourTable] where Educated = 'Yes'

answered Aug 02 '17 at 14:42

Ace Amr

108
5

1

*where ALL children for the parent are "Yes" – JNevill Aug 02 '17 at 14:43

SQL query group by and having all

4 Answers4