Re: exclusion query

On Thu, Sep 25, 2008 at 02:11:23PM +0100, Oliveiros Cristina wrote:
> Hi, Louis-David,
>
> I guess you already have your problem solved, but just for the sake of
> curiosity, another
> way to do it might be to tweak a little your original query, I've written
> on Capitals the things I've added.
> Should you need to exclude more than one event you can add the conditions
> to the commented line (ORed )

That LEFT JOIN + GROUP BY trick is wicked! :-) I spent the last half
hour struggling to understand it. You solution is a great learning tool
and you obviously know your way around SQL.

Thanks!

> Best,
> Oliveiros
>
> select distinct pt.type
> from person_type pt
> natural join person_to_event
> join event e using (id_event)
> LEFT JOIN event e2
> ON e.id_event = e2.id_event
> AND e2.id_event=219 -- put here the id of the event you wanna exclude
> join event_type et
> ON e.id_event_type = et.id_event_type
> where et.type_fr='théâtre'
> GROUP BY pt.type_fr
> HAVING SUM(e2.id_event) IS NULL;
>
> ----- Original Message ----- From: "Louis-David Mitterrand"
> <vindex+lists-pgsql-sql(at)apartia(dot)org>
> To: <pgsql-sql(at)postgresql(dot)org>
> Sent: Tuesday, September 23, 2008 9:18 AM
> Subject: Re: [SQL] exclusion query
>
>
>> On Mon, Sep 22, 2008 at 09:39:08AM -0700, Mark Roberts wrote:
>>>
>>> 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.
>>
>> Hi,
>>
>> That works very nicely (with minor adaptations).
>>
>> I also had that solution-without-a-subselect in the back of my mind but
>> this does the job just fine!
>>
>> Cheers,
>>
>> --
>> 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
>>
>
>
> --
> 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