[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] exclusion query
From: Louis-David Mitterrand <vindex+lists-pgsql-sql () apartia ! org>
Date: 2008-09-25 20:06:26
Message-ID: 20080925200625.GA12145 () apartia ! fr
[Download RAW message or body]
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic