Nested join on itself SQL server

user_13921132 0
Viewed 43

I'm trying to join two tables in SQL server, but I'm not getting the expected result. I've one table which I joined on itself to get the value for the latest timestamp for each ID reading. This I managed by following:

SELECT a.[ID_A], a.[ID_B], a.[VALUE], a.[TIMESTAMP]
FROM TABLE_NAME a
INNER JOIN  (
    SELECT [ID_A], max([TIMESTAMP]) as LATEST_TIMESTAMP
    FROM TABLE _NAME  
    GROUP BY [ID_A] 
    ) b
    ON a.[ID_A] = b.[ID_A]

I now want to join it to another table, which also contains ID_A. It's not necessary to keep any variable from this table, I only want to use this table to filter the previous selection (so, the output of the code should be filtered to contain only ID's which are included in the second table. I thought it was as easy as

SELECT a.[ID_A], a.[ID_B], a.[VALUE], a.[TIMESTAMP]
FROM TABLE_1 a
INNER JOIN  (
    SELECT [ID_A], max([TIMESTAMP]) as LATEST_TIMESTAMP
    FROM TABLE_1  
    GROUP BY [ID_A] 
    ) b
    ON a.[ID_A] = b.[ID_A]
RIGHT JOIN TABLE_2 c
ON c.[ID_A] = a.[ID_A]

But this gives me a table where ID_A has several lines, and has multiple values and timestamps (so not only the the latest one), even though the first part of the code does return only the latest value if I run it seperatly.

Anyone an idea on how to achieve this?

join nested sql-server
1 Answers

I would write your logic with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID_A ORDER BY [TIMESTAMP] DESC) rn
    FROM TABLE_1
)

SELECT a.[ID_A], a.[ID_B], a.[VALUE], a.[TIMESTAMP]
FROM cte a
LEFT JOIN TABLE_2 c
    ON a.ID_A = c.ID_A
WHERE
    a.rn = 1;

You seem to be using a right join in your final example, but you never even select anything from the second table TABLE_2. Therefore, it makes no sense to do this right join (and I would use left join anyway).

Note that the correct pattern of joining to restrict to a max value is this:

SELECT a.[ID_A], a.[ID_B], a.[VALUE], a.[TIMESTAMP]
FROM TABLE_NAME a
INNER JOIN
(
    SELECT [ID_A], MAX([TIMESTAMP]) AS LATEST_TIMESTAMP
    FROM TABLE_NAME  
    GROUP BY [ID_A]
) b
    ON a.[ID_A] = b.[ID_A] AND
       a.[TIMESTAMP] = b.LATEST_TIMESTAMP  -- note carefully this extra condition

You need an extra join condition to restrict the first outer table to only records having the max timestamp. But, in recent versions of SQL Server (past 10 years at least), we would use ROW_NUMBER instead of this join approach.

user_18632290
Related