Geek Answers Handbook

Problem with Oracle Nested Correlation of Nested Queries

Consider table1 and table2 with a one-to-many relationship (table1 is the main table, and table2 is the details table). I want to get records from table1, where some value ('XXX') is the value of the most recent record in table2 of detailed records correlated with table 1. I want to do the following:

select t1.pk_id
  from table1 t1
 where 'XXX' = (select a_col
                  from (  select a_col
                            from table2 t2
                           where t2.fk_id = t1.pk_id
                        order by t2.date_col desc)
                 where rownum = 1)

But, since the link to table1 (t1) in the correlated subquery is two-level, it appears with an Oracle error (invalid identifier t1). I need to be able to rewrite this, but one of the caveats is that only the where clause can be changed (i.e. the initial choice and from must remain unchanged). It can be done?

+5
source share
4 answers

Here's another analytic approach:

select t1.pk_id
  from table1 t1
 where 'XXX' = (select distinct first_value(t2.a_col)
                                  over (order by t2.date_col desc)
                  from table2 t2
                  where t2.fk_id = t1.pk_id)

And here is the same idea using the ranking function:

select t1.pk_id
  from table1 t1
 where 'XXX' = (select max(t2.a_col) keep
                          (dense_rank first order by t2.date_col desc)
                  from table2 t2
                  where t2.fk_id = t1.pk_id)
+6
source

you can use the analytics here: join table1 to table2, take the most recent table2 entry for each element in table1 and make sure that this last element has the value "XXX":

SELECT *
  FROM (SELECT t1.*, 
               t2.a_col, 
               row_number() over (PARTITION BY t1.pk 
                                  ORDER BY t2.date_col DESC) rnk
           FROM table1 t1
           JOIN table2 t2 ON t2.fk_id = t1.pk_id)
 WHERE rnk = 1
   AND a_col = 'XXX'

Refresh . Without changing the top-level SELECT, you can write the following query:

SELECT t1.pk_id
  FROM table1 t1
 WHERE 'XXX' =
       (SELECT a_col
          FROM (SELECT a_col, 
                       t2_in.fk_id, 
                       row_number() over(PARTITION BY t2_in.fk_id 
                                         ORDER BY t2_in.date_col DESC) rnk
                   FROM table2 t2_in) t2
         WHERE rnk = 1
           AND t2.fk_id = t1.pk_id)

Basically you only append (SEMI-JOIN) the rows from table2, which are the most recent for each fk_id

+3
source

:

select t1.pk_id   
from table1 t1  
where 'XXX' = 
(select a_col 
 from table2 t2                           
 where t2.fk_id = t1.pk_id                         
 and t2.date_col =
   (select max(t3.date_col)
    from table2 t3
    where t3.fk_id = t2.fk_id)
)
-1

, ?

select t1.pk_id
  from table1 t1
 where 'XXX' = (  select a_col
                    from table2 t2
                   where t2.fk_id = t1.pk_id
                    t2.date_col = (select max(date_col) from table2 where fk_id = t1.pk_id)
                )
-1

More articles:


All Articles