Left join to get latest record which have value in other column

kracks 0
Viewed 33

I have 3 tables

Table1
-----------
ID   NAME
1    Test1


Table2
-----------
ID   Table1_ID     Table3_ID
1    1             2
2    1             3
3    1             4
Table3
-----------
ID   NAME    DATA
2    Test1   
3    Test2   Data2
4    Test3

I have ID from Table1 and need to get latest record from Table3 which have value in DATA column

This is my query but it does not work well especially with large amount of data, it is really slow

SELECT t1.ID FROM Table1 t1
            left JOIN Table2 toa on t1.ID = toa.Table1_ID
            left JOIN Table3 toad on toa.Table3_ID = toad.ID
WHERE toad.ID = (SELECT MAX(toad2.ID)
                 FROM Table3 toad2
                 WHERE toad2.DATA is not null
                   and toad2.ID IN
                       (SELECT toa2.Table3_ID FROM Table2 toa2 WHERE toa2.Table1_ID = t1.ID))

what would be more efficiant way to get this data?

oracle11g oracle sql
2 Answers

You can simply use WINDOW function to get the LATEST record from third table -

SELECT ID
  FROM (SELECT t1.ID, ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY toad.ID DESC) RN
          FROM Table1 t1
          left JOIN Table2 toa ON t1.ID = toa.Table1_ID
          left JOIN Table3 toad ON toa.Table3_ID = toad.ID
                               AND toad.DATA IS NOT NULL
       )
WHERE RN = 1;
Ankit Bajpai0

If you were on Oracle 12c or later, you might try using an OUTER APPLY to get the latest record. Like this:

SELECT  t1.id, t1.name, toad.data
FROM    table1 t1
OUTER APPLY ( SELECT  toad.data
              FROM    table2 toa
              INNER JOIN table3 toad ON toad.id = toa.table3_id
              WHERE   toa.table1_id = t1.id
              AND     toad.data is not null
              ORDER BY toad.id DESC
              FETCH FIRST 1 ROW ONLY ) toad

Unfortunately, this feature is not available in 11g.

Matthew McPeak0
Related