Re: exclusion query

On Mon, Sep 22, 2008 at 04:34:14PM +0200, Louis-David Mitterrand wrote:
> Hi,
>
> I've got five related tables:
>
> - person_type:
> id_person_type integer
> type_fr text
>
> - person:
> id_person integer
> ...
>
> - person_to_event:
> id_person -> person
> id_person_type -> person_type (e.g: actor, director, producer, ...)
> id_event -> event
>
> - event:
> id_event integer
> id_event_type -> event_type
> ...
>
> - event_type:
> id_event_type integer
> type_fr text
>
> 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).

To be more precise: not used in a particular event _but_ used in other
events of type 'theatre'.

> 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?
>
> Thanks,
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql