How to pass main query column to nested sub query?

Question

I am writing a query, which has nested queryies in select statement as below.

Select t1.R1,
  (
    select * from
    (
      select t2.R2
      from table2 t2
      where t2.Condition_1=t1.C1
      order by t2.Condition_2 desc
    )
    where rownum=1
  ),
  t1.R3
from table1 t1 

I am trying to pass main queries object into a sub query inside a sub query in select statement.

When I execute this I am getting object invalid error at t1.C1.

I am able to pass object of the main table table1 to the first sub query, but how can I pass the table1 column to sub query inside a sub query?

Can any please help me in this scenario?

Answer 1

You can only refer to objects down to one level of subquery, so t1 just isn't recognised in the inner subquery.

There are a few ways to do this. Sticking with your current subquery, you can make that into an in-line view and join to that instead:

select t1.r1, t2.r2, t1.r3
from table1 t1
join (
  select *
  from (
    select condition_1, r2
    from table2
    order by condition_2 desc
  )
  where rownum = 1
) t2 on t2.condition_1 = t1.c1;

The subquery finds one table2 record for each condition_1, based on your ordering criteria; then that single row can be joined to a single row from table1 (assuming c1 is unique).

Or you could use an analytic function:

select r1, r2, r3
from (
  select t1.r1, t2.r2, t1.r3,
    row_number() over (partition by t2.condition_1 
      order by t2.condition_2 desc) as rn
  from table1 t1
  join table2 t2 on t2.condition_1 = t1.c1
)
where rn = 1;

This joins the two tables and then decides which of the table2 values to retain by looking at the already-joined result set, based on the ordering condition in the analytic function's windowing clause. The inner query run on its own would produce what you saw when you tried to join before, with all the 'duplicates' (not really duplicates in the result set, but multiple rows for each r1/r3 pair from table1), with an addition rn column that ranks those result set rows within those duplicates; the outer query then filters that to only shows the rows ranked first.

SQL Fiddle demo of both approaches.

If condition_2 isn't unique then you'd need to decide how to handle ties - if table2 could have two r2 values for the same condition_1 and condition_2 combination. You could look at a different analytic function in that case - rank for example.