SQL query to find all parent records where all child records have a given value (not just some)

Question

An Event has many Participants. A Participant has a field of "status".

I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'.

I can find all Events where some of its Participants have a status of 'present' with the following SQL:

SELECT events.id, participants.status as status, count(*) 
FROM `events` INNER JOIN `participants` 
ON `participants`.`event_id` = `events`.`id` 
WHERE (`participants`.`status` != 'present') 
GROUP BY participants.event_id HAVING count(*) > 0

This almost works. The problem is that if one of the participant rows has a status of something other like "away", the event will still get fetched, because the sibling records are of a status equal to "present".

I need to ensure that I am filtering out every Event record with all Participants of a status of "present".

I am using PostgreSQL, so no non-PostgreSQL specific solutions please.

Thanks.

Answer 1

I don't have a copy of PostgreSQL in front of me. Does this compile in your version:

...
HAVING count(*) = (select count(*) from participatns as x where x.event_id = events.id)

An alternative approach would be to remove from this list of all events those which have a participant who's status is not 'present', using the EXCEPT clause:

select
    event.id
from events

EXCEPT

select
    event.id
from event
INNER JOIN participants 
ON participants.event_id = events.id 
WHERE (participants.status != present)

The EXCEPT syntax is usually more efficiently expressed as NOT EXISTS or as a LEFT JOIN with the child identifer ends up as null. I'm sure you can re-factor the query to suit your needs.