Re: [SQL] Problem using Subselect results

[Dmitry Tkach]

[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>:
 

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 [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly
 



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html