Message-ID: <1059051709.3f1fd8bd630bc@stud.fbi.fh-darmstadt.de>
Date: Thu, 24 Jul 2003 15:01:49 +0200
From: oheinz@stud.fbi.fh-darmstadt.de
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
References: <3F1FCCE2.47ACF401@rodos.fzk.de>
In-Reply-To: <3F1FCCE2.47ACF401@rodos.fzk.de>
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


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

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/