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

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?

CodePudding user response：

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;

CodePudding user response：

can you please give us the approximate row count in the tables involved in the query, the explain plan. You said with large amount of data it is slow , how slow is the query and what is the expected time. Also can you join Table1 and Table3, using the id column.

CodePudding user response：

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