Re: exclusion query

On Mon, 2008-09-22 at 16:34 +0200, Louis-David Mitterrand wrote:
> 
> 
> To select person_type's used in a certain event_type I have this
> query:
> 
> select distinct pt.type 
>         from person_type pt 
>         natural join person_to_event 
>         join event e using (id_event) 
>         natural join event_type et 
>         where et.type_fr='théâtre';
> 
> Now, I'd like to select person_type's _not_ used in a certain
> particular
> event (say id_event=219).
> 
> I can see how to build a quey to that effect, but is there a more
> obvious, clean, short solution? Something that looks like the above
> query maybe?

Taking your second email into account, I came up with:

select distinct pt.type_fr
from person_to_event pte   inner join person_type using (id_person_type)
where id_person_type in (   select id_person_type   from person_to_event pte       inner join event using (id_event)
  inner join event_type using (id_event_type)   where type_fr = 'theatre'
 
) and id_person_type not in (   select id_person_type   from person_to_event   where id_event = 219
)

I feel like there's a solution involving group by tugging at the back of
my mind, but I can't quite put my finger on it.  Sorry if this isn't
quite what you're asking for.

-Mark