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

Subacute

Find those that don't have any children educated = 'no' – Rapier

Answer 1

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

Vizzone

the second part of the answer threw me off. It is not just a shortcut, but another case, beware! – Tumid

Answer 2

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

Mole

In MySQL you don't actually need the CASE. – Rapier

Answer 3

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

Hoop

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"? :) – Settlement

Answer 4

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

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

Ebeneser

*where ALL children for the parent are "Yes" – Hoop