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/
