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