SQL query to find all parent records where all child records have a given value (not just some) 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(*) &gt; 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.
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.