Обсуждение: Problem using Subselect results
Hi all, I want to use the result of a subselect as condition in another one. table1: a,b table2: a,c CREATE VIEW my_view AS SELECT b,c (SELECT a, b FROM table1 WHERE b=1) my_ab, (SELECT c FROM table2, my_ab WHERE table3.a=my_ab.a) my_c; this is just an example - i know i could cross join this one, but i need to refer to the results of several subselects in several other. does return "relation my_ab unknown". it is not just a problem of execution order - if i turn it the other way round it's still the same. Am I just trying to do something really stupid? And what for is the (necessary) AS statement for subselects, if it's not possible to access their results by that name? And as I need the result of a subselect in several other subselects it's not possible to transform them into a cascade of sub, subsub, subsubsub.... selects. Any ideas? TIA, Oliver ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
> I want to use the result of a subselect as condition in another one. > > table1: a,b > table2: a,c > > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table1 WHERE b=3D1) my_ab, > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > > this is just an example - i know i could cross join this one, but i need = > to=20 > refer to the results of several subselects in several other. > > > does return "relation my_ab unknown". it is not just a problem of executi= > on=20 > order - if i turn it the other way round it's still the same. > > Am I just trying to do something really stupid? And what for is the (nece= > ssary)=20 > AS statement for subselects, if it's not possible to access their results= > by=20 > that name? > > And as I need the result of a subselect in several other subselects it's= > not=20 > possible to transform them into a cascade of sub, subsub, subsubsub.... s= > elects. > Any ideas?=20 > Does this match your intentions:CREATE VIEW my_view AS SELECT b,c FROM(SELECT b,c FROM table2, (SELECT a, b FROM table1WHERE b=3D1) my_ab WHERE table3.a=3Dmy_ab.a) my_c; I assume the reference table3.a is a typo. Regards, Christoph
SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a = my_ab.a)) my_c; You were doing what I wanted to avoid - you are using a "SUBSUBSELECT". But (now) I believe it's not possible to refer to a subselect's resultset on the same level of hierarchy - which sounds rather meaningful - because you couldn't tell which of them was being processsed first. So I'll have to get my SELECT statement into some kind of hierarchy, which makes things a bit more complicated (with twentysomething SELECT statements) Thanks, Oliver Quoting Christoph Haller <ch@rodos.fzk.de>: > Does this match your intentions: > CREATE VIEW my_view AS SELECT b,c FROM > (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab > WHERE table3.a=3Dmy_ab.a) my_c; > I assume the reference table3.a is a typo. > > Regards, Christoph > > > I want to use the result of a subselect as condition in another one. > > > > table1: a,b > > table2: a,c > > > > CREATE VIEW my_view AS SELECT b,c > > (SELECT a, b FROM table1 WHERE b=3D1) my_ab, > > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > > > > this is just an example - i know i could cross join this one, but i > need = > > to=20 > > refer to the results of several subselects in several other. > > > > > > does return "relation my_ab unknown". it is not just a problem of > executi= > > on=20 > > order - if i turn it the other way round it's still the same. > > > > Am I just trying to do something really stupid? And what for is the > (nece= > > ssary)=20 > > AS statement for subselects, if it's not possible to access their > results= > > by=20 > > that name? > > > > And as I need the result of a subselect in several other subselects > it's= > > not=20 > > possible to transform them into a cascade of sub, subsub, > subsubsub.... s= > > elects. > > Any ideas?=20 > > ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
oheinz@stud.fbi.fh-darmstadt.de wrote: >SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT >table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a = >my_ab.a)) my_c; > >You were doing what I wanted to avoid - you are using a "SUBSUBSELECT". > > What about: CREATE VIEW my_view AS SELECT b,c from (SELECT a, b FROM table1 WHERE b=1) as my_ab, (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a This looks like what you are trying to do, and doesn't use that 'subsubselect' you were trying to avoid... BTW, what is special to the second-level subselect, compared to the first level one? Why are you trying to avoid one, butnot the other? I mean, I could understand, if you (like me) just hated subselects alltogether (then you would have converted your queryinto a join), but it looks like you don't... Dima >But (now) I believe it's not possible to refer to a subselect's resultset on >the same level of hierarchy - which sounds rather meaningful - because you >couldn't tell which of them was being processsed first. > >So I'll have to get my SELECT statement into some kind of hierarchy, which >makes things a bit more complicated (with twentysomething SELECT statements) > >Thanks, >Oliver > > >Quoting Christoph Haller <ch@rodos.fzk.de>: > > >>Does this match your intentions: >> CREATE VIEW my_view AS SELECT b,c FROM >> (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab >>WHERE table3.a=3Dmy_ab.a) my_c; >>I assume the reference table3.a is a typo. >> >>Regards, Christoph >> >> >> > > > >>>I want to use the result of a subselect as condition in another one. >>> >>>table1: a,b >>>table2: a,c >>> >>>CREATE VIEW my_view AS SELECT b,c >>>(SELECT a, b FROM table1 WHERE b=3D1) my_ab, >>>(SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; >>> >>>this is just an example - i know i could cross join this one, but i >>> >>> >>need = >> >> >>>to=20 >>>refer to the results of several subselects in several other. >>> >>> >>>does return "relation my_ab unknown". it is not just a problem of >>> >>> >>executi= >> >> >>>on=20 >>>order - if i turn it the other way round it's still the same. >>> >>>Am I just trying to do something really stupid? And what for is the >>> >>> >>(nece= >> >> >>>ssary)=20 >>>AS statement for subselects, if it's not possible to access their >>> >>> >>results= >> >> >>> by=20 >>>that name? >>> >>>And as I need the result of a subselect in several other subselects >>> >>> >>it's= >> >> >>> not=20 >>>possible to transform them into a cascade of sub, subsub, >>> >>> >>subsubsub.... s= >> >> >>>elects. >>>Any ideas?=20 >>> >>> >>> > > > >------------------------------------------------- >This mail sent through IMP: http://horde.org/imp/ > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
Quoting Dmitry Tkach <dmitry@openratings.com>: > What about: > > CREATE VIEW my_view AS SELECT b,c from > (SELECT a, b FROM table1 WHERE b=1) as my_ab, > (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a > > This looks like what you are trying to do, and doesn't use that > 'subsubselect' you were trying to avoid... I assume that with this statement postgresql will compute both subselects, do a cross join on both results an then reduce them to those who match the condition my_ac.a=my_ab.a, right? What I was trying to do is reduce the results to a minimum before joining them. It's not only two or three tables and some of them will grow big, so joining them first and reducing them later may not be such a good idea. My first try (which does not work, because I'm trying to access results of subselects on the same hierarchy level): CREATE VIEW public.aufwaende_test AS SELECT aufwaende.aufwandsid, aufwaende.auftragsid, aufwaende.taetigkeitsid, my_taetigkeiten.taetigkeit, aufwaende.mitarbeiterid, my_mitarbeiter.vorname, my_mitarbeiter.nachname, my_mitarbeiter.pknummer, aufwaende.dauer, aufwaende.datum, my_auftraege.aufgabenid, my_aufgaben.aufgabe, my_auftraege.systemid, my_systeme.system, my_systeme.kundenid, my_kunden.kunde, aufwaende.status FROM aufwaende, (SELECT auftraege_complete.systemid, auftraege_complete.aufgabenid, auftraege_complete.updatenr FROM auftraege_complete WHERE (auftraege_complete.updatenr = (SELECT max(auftraege_complete.updatenr) AS max FROM auftraege_complete WHERE ((auftraege_complete.auftragsid = aufwaende.auftragsid))))) my_auftraege, (SELECT aufgaben_complete.bezeichnung AS aufgabe FROM aufgaben_complete WHERE (aufgaben_complete.updatenr = (SELECT max(aufgaben_complete.updatenr) AS max FROM aufgaben_complete WHERE ((aufgaben_complete.aufgabenid = auftraege_complete.aufgabenid) AND (aufgaben_complete.updatenr < my_auftraege.updatenr))))) my_aufgaben, (SELECT taetigkeiten_complete.bezeichnung AS taetigkeit FROM taetigkeiten_complete WHERE (taetigkeiten_complete.updatenr = (SELECT max (taetigkeiten_complete.updatenr) AS max FROM taetigkeiten_complete WHERE ((taetigkeiten_complete.taetigkeitsid = aufwaende.taetigkeitsid) AND (taetigkeiten_complete.updatenr < my_auftraege.updatenr))))) my_taetigkeiten, (SELECT systeme_complete.name AS system, systeme_complete.kundenid, systeme_complete.systemid AS sys_sysid FROM systeme_complete WHERE (systeme_complete.updatenr = (SELECT max(systeme_complete.updatenr) AS max FROM systeme_complete WHERE ((systeme_complete.systemid = auftraege_complete.systemid) AND (systeme_complete.updatenr < my_auftraege.updatenr))))) my_systeme, (SELECT kunden_complete.name AS kunde FROM kunden_complete WHERE (kunden_complete.updatenr = (SELECT max(kunden_complete.updatenr) AS max FROM kunden_complete WHERE ((kunden_complete.kundenid = my_systeme.kundenid) AND (kunden_complete.updatenr < aufwaende.updatenr))))) my_kunden, (SELECT mitarbeiter_complete.nachname, mitarbeiter_complete.vorname, mitarbeiter_complete.pknummer FROM mitarbeiter_complete WHERE (mitarbeiter_complete.updatenr = (SELECT max(mitarbeiter_complete.updatenr) AS max FROM mitarbeiter_complete WHERE ((mitarbeiter_complete.mitarbeiterid = aufwaende.mitarbeiterid) AND (mitarbeiter_complete.updatenr < my_auftraege.updatenr))))) my_mitarbeiter; as you can see most of them use my_auftraege.updatenr as one condition, and the subselect on kunden_complete uses results from the my_systeme subselect (my_systeme.kundenid) Now I see two possibilities - join the early and reduce them later - create a hierarchy so that (sub...)selects which rely on the result of another select include this select-statement as a (sub...)subselect. Any better Ideas? TIA, Oliver > > BTW, what is special to the second-level subselect, compared to the first > level one? Why are you trying to avoid one, but not the other? > > I mean, I could understand, if you (like me) just hated subselects > alltogether (then you would have converted your query into a join), but it > looks like you don't... > > Dima > > > > > >But (now) I believe it's not possible to refer to a subselect's resultset on > > >the same level of hierarchy - which sounds rather meaningful - because you > > >couldn't tell which of them was being processsed first. > > > >So I'll have to get my SELECT statement into some kind of hierarchy, which > > >makes things a bit more complicated (with twentysomething SELECT > statements) > > > >Thanks, > >Oliver > > > > > >Quoting Christoph Haller <ch@rodos.fzk.de>: > > > > > >>Does this match your intentions: > >> CREATE VIEW my_view AS SELECT b,c FROM > >> (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab > >>WHERE table3.a=3Dmy_ab.a) my_c; > >>I assume the reference table3.a is a typo. > >> > >>Regards, Christoph > >> > >> > >> > > > > > > > >>>I want to use the result of a subselect as condition in another one. > >>> > >>>table1: a,b > >>>table2: a,c > >>> > >>>CREATE VIEW my_view AS SELECT b,c > >>>(SELECT a, b FROM table1 WHERE b=3D1) my_ab, > >>>(SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > >>> > >>>this is just an example - i know i could cross join this one, but i > >>> > >>> > >>need = > >> > >> > >>>to=20 > >>>refer to the results of several subselects in several other. > >>> > >>> > >>>does return "relation my_ab unknown". it is not just a problem of > >>> > >>> > >>executi= > >> > >> > >>>on=20 > >>>order - if i turn it the other way round it's still the same. > >>> > >>>Am I just trying to do something really stupid? And what for is the > >>> > >>> > >>(nece= > >> > >> > >>>ssary)=20 > >>>AS statement for subselects, if it's not possible to access their > >>> > >>> > >>results= > >> > >> > >>> by=20 > >>>that name? > >>> > >>>And as I need the result of a subselect in several other subselects > >>> > >>> > >>it's= > >> > >> > >>> not=20 > >>>possible to transform them into a cascade of sub, subsub, > >>> > >>> > >>subsubsub.... s= > >> > >> > >>>elects. > >>>Any ideas?=20 > >>> > >>> > >>> > > > > > > > >------------------------------------------------- > >This mail sent through IMP: http://horde.org/imp/ > > > >---------------------------(end of broadcast)--------------------------- > >TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > > ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
oheinz@stud.fbi.fh-darmstadt.de writes:
> Quoting Dmitry Tkach <dmitry@openratings.com>:
>> CREATE VIEW my_view AS SELECT b,c from
>> (SELECT a, b FROM table1 WHERE b=1) as my_ab,
>> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
> I assume that with this statement postgresql will compute both subselects, do
> a cross join on both results an then reduce them to those who match the
> condition my_ac.a=my_ab.a, right?
No, it's smarter than that.
I tried the experiment in 7.3 and CVS tip, using some tables from the
regression database:
regression=# create view my_view as select b,c from
regression-# (select unique1,unique2 from tenk1 where unique2=1) as
regression-# my_ab(a,b),
regression-# (select unique1,unique2 from onek) as my_ac(a,c)
regression-# where my_ac.a = my_ab.a;
CREATE VIEW
regression=# explain select * from my_view; QUERY PLAN
---------------------------------------------------------------------------------Nested Loop (cost=0.00..24.47 rows=1
width=16) -> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..3.01 rows=1 width=8) Index Cond: (unique2 =
1) -> Index Scan using onek_unique1 on onek (cost=0.00..21.40 rows=5 width=8) Index Cond: (onek.unique1 =
"outer".unique1)
(5 rows)
regression=#
Looks like a fairly decent plan to me. It's certainly not letting the
sub-select structure get in its way.
regards, tom lane
oheinz@stud.fbi.fh-darmstadt.de wrote: >Quoting Dmitry Tkach <dmitry@openratings.com>: > > >>What about: >> >>CREATE VIEW my_view AS SELECT b,c from >>(SELECT a, b FROM table1 WHERE b=1) as my_ab, >>(SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a >> >>This looks like what you are trying to do, and doesn't use that >>'subsubselect' you were trying to avoid... >> >> > >I assume that with this statement postgresql will compute both subselects, do >a cross join on both results an then reduce them to those who match the >condition my_ac.a=my_ab.a, right? > > I don't think so... Not totally sure, but I believe, that, at least in this case, the query plan will be equivalent to a join... >What I was trying to do is reduce the results to a minimum before joining them. >It's not only two or three tables and some of them will grow big, so joining >them first and reducing them later may not be such a good idea. > I am not sure I understand what you mean by 'reducing'. It seems to me that you could make your query a lot simpler by converting it into a join, and I don't see anything you are buying by those subselects.... Also, you may want to get rid of max(), and replace those things with 'select column from table order by column desc limit 1'. This should be a lot quicker (provided that you have an index on that column). Dima > > >My first try (which does not work, because I'm trying to access results of >subselects on the same hierarchy level): > > > >CREATE VIEW public.aufwaende_test AS SELECT aufwaende.aufwandsid, >aufwaende.auftragsid, aufwaende.taetigkeitsid, my_taetigkeiten.taetigkeit, >aufwaende.mitarbeiterid, my_mitarbeiter.vorname, my_mitarbeiter.nachname, >my_mitarbeiter.pknummer, aufwaende.dauer, aufwaende.datum, >my_auftraege.aufgabenid, my_aufgaben.aufgabe, my_auftraege.systemid, >my_systeme.system, my_systeme.kundenid, my_kunden.kunde, aufwaende.status FROM >aufwaende, > >(SELECT auftraege_complete.systemid, auftraege_complete.aufgabenid, >auftraege_complete.updatenr FROM auftraege_complete WHERE >(auftraege_complete.updatenr = (SELECT max(auftraege_complete.updatenr) AS max >FROM auftraege_complete WHERE ((auftraege_complete.auftragsid = >aufwaende.auftragsid))))) my_auftraege, > >(SELECT aufgaben_complete.bezeichnung AS aufgabe FROM aufgaben_complete WHERE >(aufgaben_complete.updatenr = (SELECT max(aufgaben_complete.updatenr) AS max >FROM aufgaben_complete WHERE ((aufgaben_complete.aufgabenid = >auftraege_complete.aufgabenid) AND (aufgaben_complete.updatenr < >my_auftraege.updatenr))))) my_aufgaben, > >(SELECT taetigkeiten_complete.bezeichnung AS taetigkeit FROM >taetigkeiten_complete WHERE (taetigkeiten_complete.updatenr = (SELECT max >(taetigkeiten_complete.updatenr) AS max FROM taetigkeiten_complete WHERE >((taetigkeiten_complete.taetigkeitsid = aufwaende.taetigkeitsid) AND >(taetigkeiten_complete.updatenr < my_auftraege.updatenr))))) my_taetigkeiten, > >(SELECT systeme_complete.name AS system, systeme_complete.kundenid, >systeme_complete.systemid AS sys_sysid FROM systeme_complete WHERE >(systeme_complete.updatenr = (SELECT max(systeme_complete.updatenr) AS max FROM >systeme_complete WHERE ((systeme_complete.systemid = >auftraege_complete.systemid) AND (systeme_complete.updatenr < >my_auftraege.updatenr))))) my_systeme, > >(SELECT kunden_complete.name AS kunde FROM kunden_complete WHERE >(kunden_complete.updatenr = (SELECT max(kunden_complete.updatenr) AS max FROM >kunden_complete WHERE ((kunden_complete.kundenid = my_systeme.kundenid) AND >(kunden_complete.updatenr < aufwaende.updatenr))))) my_kunden, > >(SELECT mitarbeiter_complete.nachname, mitarbeiter_complete.vorname, >mitarbeiter_complete.pknummer FROM mitarbeiter_complete WHERE >(mitarbeiter_complete.updatenr = (SELECT max(mitarbeiter_complete.updatenr) AS >max FROM mitarbeiter_complete WHERE ((mitarbeiter_complete.mitarbeiterid = >aufwaende.mitarbeiterid) AND (mitarbeiter_complete.updatenr < >my_auftraege.updatenr))))) my_mitarbeiter; > > >as you can see most of them use my_auftraege.updatenr as one condition, and the >subselect on kunden_complete uses results from the my_systeme subselect >(my_systeme.kundenid) > > > >Now I see two possibilities > >- join the early and reduce them later >- create a hierarchy so that (sub...)selects which rely on the result of >another select include this select-statement as a (sub...)subselect. > > >Any better Ideas? > >TIA, >Oliver > > > > >>BTW, what is special to the second-level subselect, compared to the first >>level one? Why are you trying to avoid one, but not the other? >> >>I mean, I could understand, if you (like me) just hated subselects >>alltogether (then you would have converted your query into a join), but it >>looks like you don't... >> >>Dima >> >> >> >> >> >> >>>But (now) I believe it's not possible to refer to a subselect's resultset on >>> >>> >>>the same level of hierarchy - which sounds rather meaningful - because you >>> >>> >>>couldn't tell which of them was being processsed first. >>> >>>So I'll have to get my SELECT statement into some kind of hierarchy, which >>> >>> >>>makes things a bit more complicated (with twentysomething SELECT >>> >>> >>statements) >> >> >>>Thanks, >>>Oliver >>> >>> >>>Quoting Christoph Haller <ch@rodos.fzk.de>: >>> >>> >>> >>> >>>>Does this match your intentions: >>>>CREATE VIEW my_view AS SELECT b,c FROM >>>>(SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab >>>>WHERE table3.a=3Dmy_ab.a) my_c; >>>>I assume the reference table3.a is a typo. >>>> >>>>Regards, Christoph >>>> >>>> >>>> >>>> >>>> >>> >>> >>> >>> >>>>>I want to use the result of a subselect as condition in another one. >>>>> >>>>>table1: a,b >>>>>table2: a,c >>>>> >>>>>CREATE VIEW my_view AS SELECT b,c >>>>>(SELECT a, b FROM table1 WHERE b=3D1) my_ab, >>>>>(SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; >>>>> >>>>>this is just an example - i know i could cross join this one, but i >>>>> >>>>> >>>>> >>>>> >>>>need = >>>> >>>> >>>> >>>> >>>>>to=20 >>>>>refer to the results of several subselects in several other. >>>>> >>>>> >>>>>does return "relation my_ab unknown". it is not just a problem of >>>>> >>>>> >>>>> >>>>> >>>>executi= >>>> >>>> >>>> >>>> >>>>>on=20 >>>>>order - if i turn it the other way round it's still the same. >>>>> >>>>>Am I just trying to do something really stupid? And what for is the >>>>> >>>>> >>>>> >>>>> >>>>(nece= >>>> >>>> >>>> >>>> >>>>>ssary)=20 >>>>>AS statement for subselects, if it's not possible to access their >>>>> >>>>> >>>>> >>>>> >>>>results= >>>> >>>> >>>> >>>> >>>>>by=20 >>>>>that name? >>>>> >>>>>And as I need the result of a subselect in several other subselects >>>>> >>>>> >>>>> >>>>> >>>>it's= >>>> >>>> >>>> >>>> >>>>>not=20 >>>>>possible to transform them into a cascade of sub, subsub, >>>>> >>>>> >>>>> >>>>> >>>>subsubsub.... s= >>>> >>>> >>>> >>>> >>>>>elects. >>>>>Any ideas?=20 >>>>> >>>>> >>>>> >>>>> >>>>> >>> >>>------------------------------------------------- >>>This mail sent through IMP: http://horde.org/imp/ >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 3: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so that your >>> message can get through to the mailing list cleanly >>> >>> >>> >>> >> >> > > > > >------------------------------------------------- >This mail sent through IMP: http://horde.org/imp/ > >