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

Question

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 1

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

Answer 2

I assume you are using SQL Server. It is similar to Giorgos Betsos post

Declare @Table1 Table(Date1 date)
Insert into @Table1 values
('2016-06-01')
,('2016-06-02')
,('2016-06-03')
,('2016-06-04')
,('2016-06-05')
,('2016-06-06')
,('2016-06-07')

Declare @Table2 Table(Date2 date)
Insert into @Table2 values

('2016-06-01')
,('2016-06-05')
,('2016-06-07')

SELECT Date1, (SELECT top 1 Date2
               FROM @Table2
               WHERE Date2 <= t1.Date1
               order by Date2 desc) AS Date2
FROM @table1 AS t1