Обсуждение: Help to simplify sample query

Hi guys, i have a new question about how to simplify a query. I have 
the tables area_course(cd_area,cd_course) and 
teacher_course(cd_teacher,cd_course) and a set of pairs of values 
{(1,2),(98,45),(11,0),...}.
   Now, i must to select the areas which courses appears in 
teacher_course and match with the set of pairs. Something like :

select cd_area from area a1 where  exists (select * from teacher_course c2 where c2.cd_course = 
a1.cd_course and c2.cd_teacher = 1 and c2.cd_course = 2) and  exists (select * from teacher_course c2 where
c2.cd_course= 
 
a1.cd_course and c2.cd_teacher = 98 and c2.cd_course = 45) and  exists (select * from teacher_course c2 where
c2.cd_course= 
 
a1.cd_course and c2.cd_teacher = 11 and c2.cd_course = 0) and
....
   This is just a sample. The whole query is giant and its use other 
tables/columns. Is there a best way to write this query ?
   I don't think this is possible, but: Is there a efficient way to 
compare if the result of a query is a superset of the result of another ?



   I would appreciate any ideas/suggestions. Thanks !!!

Dnia 2004-03-09 06:41, Użytkownik Daniel Henrique Alves Lima napisał:
>    Hi guys, i have a new question about how to simplify a query. I have 
> the tables area_course(cd_area,cd_course) and 
> teacher_course(cd_teacher,cd_course) and a set of pairs of values 
> {(1,2),(98,45),(11,0),...}.
> 
>    Now, i must to select the areas which courses appears in 
> teacher_course and match with the set of pairs. Something like :
> 
> select cd_area from area a1 where
>   exists (select * from teacher_course c2 where c2.cd_course = 
> a1.cd_course and c2.cd_teacher = 1 and c2.cd_course = 2) and
>   exists (select * from teacher_course c2 where c2.cd_course = 
> a1.cd_course and c2.cd_teacher = 98 and c2.cd_course = 45) and
>   exists (select * from teacher_course c2 where c2.cd_course = 
> a1.cd_course and c2.cd_teacher = 11 and c2.cd_course = 0) and
> ....
> 
>    This is just a sample. The whole query is giant and its use other 
> tables/columns. Is there a best way to write this query ?

Can you try this query ? :

select cd_area from area a1 join teacher_course c2 using (cd_course)
where (cd_teacher,cd_course) in (select 1,2 union select 98,45 union 
select 11,0);

Regards,
Tomasz Myrta

Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br> writes:
>     I don't think this is possible, but: Is there a efficient way to 
> compare if the result of a query is a superset of the result of another ?

Perhaps do "SELECT foo EXCEPT SELECT bar" and see if you get anything?
        regards, tom lane

Tomasz Myrta wrote:

>
> Can you try this query ? :
>
> select cd_area from area a1
>  join teacher_course c2 using (cd_course)
> where (cd_teacher,cd_course) in (select 1,2 union select 98,45 union 
> select 11,0);
   Tomasz, the "in" in the query above will work like an "or" 
((cd_teacher=1,cd_course=2) or (cd_teacher=98,cd_course=45) or 
(cd_teacher=11,cd_course=0)) but i need an "and" behaviour (because of 
this i was using some exists/ands)...

Thanks for your reply.

How about this:

select cd_area from area a1 where  not exists ((select 1,2 union select 98,45 union select 11,0) except  select
c2.cd_teacher,c2.cd_coursefrom teacher_course c2 where 
 
c2.cd_course=a1.course)

?

Tom Lane wrote:

>Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br> writes:
>  
>
>>    I don't think this is possible, but: Is there a efficient way to 
>>compare if the result of a query is a superset of the result of another ?
>>    
>>
>
>Perhaps do "SELECT foo EXCEPT SELECT bar" and see if you get anything?
>
>            regards, tom lane
>
>  
>

Where can i find the documentation about "except" or "textcat" ?    I've looked at html docs in my machine (i've a
7.34copy) and i 
 
couldn't found information/sample of them.
                           Thanks.

On Tue, Mar 09, 2004 at 17:13:37 -0300, Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br> wrote:
>    Where can i find the documentation about "except" or "textcat" ?
>     I've looked at html docs in my machine (i've a 7.34 copy) and i 
> couldn't found information/sample of them.

except should be under the select command.
I don't think there is much on textcat as such as people are expected to
use the concatenation operator normally.