Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 –  a_horse_with_no_name May 17 at 8:57

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Yes, this is a help. Thanks for the answer. –  seder-mede May 17 at 5:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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