pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp to simplify sample query
7+ messages / 4 participants
[nested] [flat]
* Help to simplify sample query
@ 2004-03-09 05:41 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 2 replies; 7+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-09 05:41 UTC (permalink / raw)
To: pgsql-sql
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 !!!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help to simplify sample query
@ 2004-03-09 06:24 Tomasz Myrta <jasiek@klaster.net>
parent: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
1 sibling, 1 reply; 7+ messages in thread
From: Tomasz Myrta @ 2004-03-09 06:24 UTC (permalink / raw)
To: email_daniel_h@yahoo.com.br; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help to simplify sample query
@ 2004-03-09 06:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
1 sibling, 2 replies; 7+ messages in thread
From: Tom Lane @ 2004-03-09 06:27 UTC (permalink / raw)
To: email_daniel_h@yahoo.com.br; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help to simplify sample query
@ 2004-03-09 12:51 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 7+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-09 12:51 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help to simplify sample query
@ 2004-03-09 13:14 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-09 13:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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_course from 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help to simplify sample query
@ 2004-03-09 20:13 Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Daniel Henrique Alves Lima @ 2004-03-09 20:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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.
Thanks.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help to simplify sample query
@ 2004-03-09 20:18 Bruno Wolff III <bruno@wolff.to>
parent: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
0 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2004-03-09 20:18 UTC (permalink / raw)
To: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-03-09 20:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 05:41 Help to simplify sample query Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 06:24 ` Tomasz Myrta <jasiek@klaster.net>
2004-03-09 12:51 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 06:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-09 13:14 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 20:13 ` Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
2004-03-09 20:18 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox