Thread: 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.