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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql