Re: exclusion query - Mailing list pgsql-sql

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