Re: Problem using Subselect results

Quoting Dmitry Tkach <dmitry(at)openratings(dot)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(at)rodos(dot)fzk(dot)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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> >
>
>

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/