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

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