Re: Problem using Subselect results - Mailing list pgsql-sql

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/
>  
>