Message-ID: <1059123702.3f20f1f689c61@stud.fbi.fh-darmstadt.de>
Date: Fri, 25 Jul 2003 11:01:42 +0200
From: oheinz@stud.fbi.fh-darmstadt.de
To: Dmitry Tkach <dmitry@openratings.com>
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
References: <3F1FCCE2.47ACF401@rodos.fzk.de>
	<1059051709.3f1fd8bd630bc@stud.fbi.fh-darmstadt.de>
	<3F1FF331.7000106@openratings.com>
In-Reply-To: <3F1FF331.7000106@openratings.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.2.1

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/