2

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.

1
  • The syntax of the statement you have show is invalid for Postgres (dreaded backticks, invalid use of group by). That looks more like you are working with MySQL
    – user1822
    May 17, 2014 at 8:57

1 Answer 1

3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.