user6532473 user6532473 - 5 months ago 8
SQL Question

How to join two tables by using the first relevant date?

I would like to join to two tables using the date as the link, however, I do not want an exact match. The first table has more dates than the second one so I want the match to be dates from the second table to 'closest' date.

Example

Table 1:

Date1
2016-06-01
2016-06-02
2016-06-03
2016-06-04
2016-06-05
2016-06-06
2016-06-07


Table 2:

Date2
2016-06-01
2016-06-05
2016-06-07


Joined Table

Date1 Date2
2016-06-01 2016-06-01
2016-06-02 2016-06-01
2016-06-03 2016-06-01
2016-06-04 2016-06-01
2016-06-05 2016-06-05
2016-06-06 2016-06-05
2016-06-07 2016-06-07


Hope that makes sense.

Thanks,

SR

Answer

In MySql you can use a correlated subquery:

SELECT Date1, (SELECT Date2
               FROM Table2
               WHERE Date2 <= t1.Date1
               ORDER BY Date2 DESC LIMIT 1) AS Date2
FROM table1 AS t1 

Demo here

In SQL Server you can use CROSS APPLY:

SELECT t1.Date1, t2.Date2
FROM table1 AS t1
CROSS APPLY (
   SELECT TOP 1 Date2
   FROM table2
   WHERE Date2 <= t1.Date1 
   ORDER BY Date2 DESC) AS t2
Comments