Left Join two tables on closest earlier date-CodePudding

I have two tables, table_A and table_B. I want to do a left join on both tables so that each record in table_A is matched with a record in table_B using the date as a key. However some dates in A will be missing in B and vice-versa. So my goal is to join A and B using the closest earlier date in B.

There is one other condition I need to join on. So take the following code for example:

  SELECT a.*, b.* 
  FROM table_A a
  LEFT JOIN table_B b ON a.product = b.product AND a.date = b.date

If the dates were exactly the same, I could use the above code. However they're not, so I want the closest lesser date in table_B to use a match for table_A.

Based on some solutions I saw, I tried the following:

  SELECT a.*, b.* 
  FROM table_A a
  LEFT JOIN table_B b ON a.product = b.product 
  AND a.date = (SELECT MAX(date) FROM table_B WHERE date <= a.date)

I thought this would work becuase I am searching for the max date in table_B that is still less than the date in table_A, however I returning nulls for the fields in table_B.

Perhaps it has to do with the other condition which is a.product = b.product?

CodePudding user response：

I would try something with an outer apply / select top 1 approach as a starting point.

select a.*, b.*
from table_A as [a]
    outer apply
    (
        select top 1 c.*
        from table_B as [c]
        where c.product = a.product
            and c.date <= a.date
        order by c.date desc
    ) as [b]

CodePudding user response：

SELECT product_id, dat_1, dat_2 FROM (
    SELECT t1.product_id, t1.dat as dat_1, t2.dat as dat_2,
        row_number() over(partition by t1.product_id order by t2.dat desc) as rn
    FROM table_1 t1
    LEFT JOIN table_2 t2 ON t1.product_id = t2.product_id AND t1.dat >= t2.dat
)
WHERE rn = 1 ;