CODEWITHSUNDEEP
mysqljoin
Elias Schoof
Elias Schoof

Reputation: 2046

MySQL: Join on most recent date

I have a table containing dates and a value: 

table_1
-------
dt           x
2018-01-01   1
2018-01-06   4
2018-01-07   2
2018-01-12   3

And I have another table containing dates:

table_2
-------
dt
2018-01-01
2018-01-03
2018-01-04
2018-01-06
2018-01-08
2018-01-09
2018-01-11

I want to create a new table my_joined_table that

So the result should be:

my_joined_table
---------------
dt           x
2018-01-01   1
2018-01-03   1
2018-01-04   1
2018-01-06   4
2018-01-08   2
2018-01-09   2
2018-01-11   2

This is probably a standard problem, but I cannot seem to figure it out. Any help is appreciated.

Upvotes: 0

Views: 135

Answers (1)

Nick
Nick

Reputation: 147146

This query will give you the result you want. It JOINs table_2 to table_1 on table_1 having the maximum dt less than or equal to the table_2 dt value:

SELECT t2.dt, t1.x
FROM table_2 t2
JOIN table_1 t1 ON t1.dt = (SELECT MAX(dt) FROM table_1 WHERE table_1.dt <= t2.dt)

Output:

dt          x
2018-01-01  1
2018-01-03  1
2018-01-04  1
2018-01-06  4
2018-01-08  2
2018-01-09  2
2018-01-11  2

To create your my_joined_table table, just use a CREATE TABLE ... SELECT query:

CREATE TABLE my_joined_table AS
SELECT t2.dt, t1.x
FROM table_2 t2
JOIN table_1 t1 ON t1.dt = (SELECT MAX(dt) FROM table_1 WHERE table_1.dt <= t2.dt)

Demo on dbfiddle

Upvotes: 1

Related Questions