[EMAIL PROTECTED] wrote:
Quoting Dmitry Tkach <[EMAIL PROTECTED]>: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 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.I am not sure I understand what you mean by 'reducing'.
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.
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 onstatements)
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
Thanks, Oliver
Quoting Christoph Haller <[EMAIL PROTECTED]>:
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.need =
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
executi=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
(nece=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
ssary)=20results=
AS statement for subselects, if it's not possible to access their
it's=by=20 that name?
And as I need the result of a subselect in several other subselects
not=20subsubsub.... s=
possible to transform them into a cascade of sub, subsub,
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html