CODEWITHSUNDEEP
oracle-databasesubquery
simplfuzz
simplfuzz

Reputation: 12895

Oracle: Using results of one subquery inside another

select t1Joint2.c1,t1Joint3.c3 from
(select * from table1 where name  = "some") t1,
(select t1.c1,t2.c2 from table2 t2 where t1.c1 = t2.c2) t1Joint2,
(select t1.c1,t3.c3 from table3 t3 where t1.c1 = t3.c3) t1Joint3,
;

The above query doesn't work in Oracle.
Any workaround?

Using Oracle 11g.

Upvotes: 0

Views: 2882

Answers (2)

schurik
schurik

Reputation: 7928

with t1 as 
  (select * from table1 where name  = 'some')
, t1Joint2 as
  (select t1.c1,t2.c2 from t1,  table2 t2 where t1.c1 = t2.c2)
, t1Joint3 as
  (select t1.c1, t3.c3 from t1, table3 t3 where t1.c1 = t3.c3) 
select t1Joint2.c1,t1Joint3.c3 
from t1Joint2, t1Joint3
;

Upvotes: 1

Codo
Codo

Reputation: 78825

Why not write it as:

select t1.c1, t3.c3
from table1 t1
join table2 t2 on t1.c1 = t2.c2
join table3 t3 on t1.c1 = t3.c3
where name = "some"

Upvotes: 5

Related Questions