I am trying write a SQL query to pull the data from 3 tables using JOINS on basis of common HashKey and I want to take all the updated records from 3rd table based on the load date(last increment/recent records) using SQL.

I have tried below SQL query but I am not able to get the recent record from third table.

SELECT
    tab1.TennisID    
    tab1.TennisHashKey    
    tab3.LoadDate    
    tab2.TennisType    
    tab3.Clicks    
    tab3.Hit    
    tab3.Likes   
 fROM table1 tab1
 LEFT JOIN table2 tab2
     ON tab1.TennisHashKey = tab2.TennisHashKey
 LEFT JOIN (SELECT * FROM Table3 WHERE LoadDate = (SELECT TOP 1 LoadDate FROM Table 3 ORDER BY LoadDate Desc)) tab3
     ON tab2.TennisHashKey = tab3.TennishHashKey

I have matching number of records in Table 1 and Table 2, but there are multiple rows for same hashkey in Table3 based on loadDate.

Please provide your suggestion on this.

Thanks

CodePudding user response：

Use ROW_NUMBER() to join only the most recent row from Table3.

SELECT
    tab1.TennisID    
    , tab1.TennisHashKey    
    , tab3.LoadDate    
    , tab2.TennisType    
    , tab3.Clicks    
    , tab3.Hit    
    , tab3.Likes   
FROM table1 tab1
LEFT JOIN table2 tab2
    ON tab1.TennisHashKey = tab2.TennisHashKey
LEFT JOIN (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY TennisHashKey ORDER BY LoadDate DESC) rn
    FROM Table3
) tab3
    ON tab2.TennisHashKey = tab3.TennishHashKey
    AND rn = 1;

CodePudding user response：

Another approach: you can use OUTER APPLY and get latest row and select it.

declare @table1 table(tennisid char(1), tennishashkey char(4),loaddate date)
declare @table2 table(tennishashkey char(4),tennistype char(10), loaddate date)
declare @table3 table(tennishashkey char(4),loaddate date,clicks int, hit int, likes int)

insert into @table1 values('A','A001','2020-01-01')
insert into @table2 values('A001','grass','2020-01-01')
insert into @table3 values('A001','2020-01-01',0,0,0),('A001','2020-01-01',1,1,1);

SELECT
    tab1.TennisID    
    , tab1.TennisHashKey    
    , tab3.LoadDate    
    , tab2.TennisType    
    , tab3.Clicks    
    , tab3.Hit    
    , tab3.Likes   
FROM @table1 tab1
LEFT JOIN @table2 tab2
    ON tab1.TennisHashKey = tab2.TennisHashKey
OUTER APPLY (
    SELECT TOP 1 * 
    FROM @Table3 as tab3
    where tab3.tennishashkey = tab1.tennishashkey
    order by loaddate desc
) tab3