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

Re: exclusion query

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Louis-David Mitterrand" <vindex+lists-pgsql-sql(at)apartia(dot)org>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: exclusion query
Date: 2008-09-25 13:11:23
Message-ID: 00fe01c91f10$355328f0$ec5a3d0a@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-sql
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 )

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
> 


In response to

Responses

pgsql-sql by date

Next:From: Louis-David MitterrandDate: 2008-09-25 20:06:26
Subject: Re: exclusion query
Previous:From: Tom LaneDate: 2008-09-24 02:25:23
Subject: Re: Special grouping on sorted data.

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