Find Participants who have always cancelled

[drewson]

Hi,

I have two tables, participants and events. For every participant, there are many events. I am trying to figure out how to select all of the participants who have always cancelled events. There is a "Cancelled" boolean field in the events table which is True or False, so I need to select all participants who have all of their event records set to True.

Any ideas?

Thank you!
Drew

 

[kaht]

[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] p.id
[COLOR=blue]from[/color] participants p
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] [COLOR=blue]events[/color] e1
   [COLOR=blue]on[/color] p.id = e1.id
      and e1.cancelled = 1
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] [COLOR=blue]events[/color] e2
   [COLOR=blue]on[/color] p.id = e2.id
      and e2.cancelled = 0
[COLOR=blue]where[/color] p2.id [COLOR=blue]is[/color] null

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[philhege]

Does the participant cancel their participation in the event, or is the event canceled in its entirety?

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[drewson]

The event is cancelled by individual, the entire event is not cancelled.

Thanks!

 

[philhege]

Can we see the table schema and field explanations, please?

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[ESquared]

If the cancelled column is in the events table, how does it apply to the participant and not the event?

Are you sure you don't have an intermediate join table between participants and events, and this is the table with the cancelled column? That seems the correct design to me.

Then it is a simple query: show all participants in at least one event, for whom no non-cancelled event exists.:

select
   p.participantid
from
   participants p
where
   not exists (
      select *
      from participantevents pe
      where
         pe.participantid = p.participantid
         and pe.cancelled = 0
   ) and exists (
      select *
      from participantevents pe
      where pe.participantid = p.participantid
   )

The query would be simpler if you also wanted participants who've never had any events, or if there was some way to tell from the participants table that they have ever had an event (such as LastEventDate or something).

The semi-join and anti-semi join above (those subqueries are interpreted as joins) may perform well, though I can think of another way to do it, testing is in order to see which is actually better:

select
   p.participantid
from
   participants p
   inner join participantevents pe on p.participantid = pe.participantid
group by
   p.participantid
having
   sum(case cancelled when 0 then 1 else 0 end) = 0

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]