Skip site navigation (1) Skip section navigation (2)

Re: exclusion query

From: Mark Roberts <mailing_lists(at)pandapocket(dot)com>
To: Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: exclusion query
Date: 2008-09-22 16:39:08
Message-ID: 1222101548.12105.236.camel@localhost (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: Mike ToewsDate: 2008-09-22 21:56:36
Subject: Multi-line text fields
Previous:From: Louis-David MitterrandDate: 2008-09-22 14:45:03
Subject: Re: exclusion query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group