Обсуждение: exclusion query

Поиск
Список
Период
Сортировка

exclusion query

От
Louis-David Mitterrand
Дата:
Hi,

I've got five related tables:

- person_type:id_person_type     integertype_fr            text

- person:id_person         integer...

- person_to_event:id_person         -> personid_person_type     -> person_type (e.g: actor, director, producer,
...)id_event       -> event
 

- event:id_event        integerid_event_type    -> event_type...

- event_type:id_event_type    integertype_fr            text

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?

Thanks,


Re: exclusion query

От
Louis-David Mitterrand
Дата:
On Mon, Sep 22, 2008 at 04:34:14PM +0200, Louis-David Mitterrand wrote:
> Hi,
> 
> I've got five related tables:
> 
> - person_type:
>     id_person_type     integer
>     type_fr            text
> 
> - person:
>     id_person         integer
>     ...
> 
> - person_to_event:
>     id_person         -> person
>     id_person_type     -> person_type (e.g: actor, director, producer, ...)
>     id_event        -> event
> 
> - event:
>     id_event        integer
>     id_event_type    -> event_type
>     ...
> 
> - event_type:
>     id_event_type    integer
>     type_fr            text
> 
> 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).

To be more precise: not used in a particular event _but_ used in other
events of type 'theatre'.

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


Re: exclusion query

От
Mark Roberts
Дата:
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



Re: exclusion query

От
Louis-David Mitterrand
Дата:
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,


Re: exclusion query

От
"Oliveiros Cristina"
Дата:
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.typefrom person_type ptnatural 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
> 



Re: exclusion query

От
Louis-David Mitterrand
Дата:
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