Re: Problem using Subselect results

oheinz(at)stud(dot)fbi(dot)fh-darmstadt(dot)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, 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
>
>