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.