Message-ID: <404D6334.4030501@klaster.net>
Date: Tue, 09 Mar 2004 07:24:52 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.6) Gecko/20040113
MIME-Version: 1.0
To: email_daniel_h@yahoo.com.br
Cc: pgsql-sql@postgresql.org
Subject: Re: Help to simplify sample query
References: <404D5920.40104@yahoo.com.br>
In-Reply-To: <404D5920.40104@yahoo.com.br>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

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