From: Dmitry Tkach <dmitry@openratings.com>
To: oheinz@stud.fbi.fh-darmstadt.de
Cc: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
Date: Thu, 24 Jul 2003 10:54:41 -0400
Message-ID: <3F1FF331.7000106@openratings.com> (raw)
In-Reply-To: <1059051709.3f1fd8bd630bc@stud.fbi.fh-darmstadt.de>
References: <3F1FCCE2.47ACF401@rodos.fzk.de>
<1059051709.3f1fd8bd630bc@stud.fbi.fh-darmstadt.de>
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, 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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, oheinz@stud.fbi.fh-darmstadt.de, ch@rodos.fzk.de
Subject: Re: Problem using Subselect results
In-Reply-To: <3F1FF331.7000106@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox