How to join MySQL tables on date range?

Question

I have a source table (TableA) that contains multiple records for each day. I need to left join (on the date field) it to TableB that contains a few records per year.

The problem is that TableA should be joined to the earliest record from TableB where the date from TableA <= the date from TableB.

CREATE TABLE IF NOT EXISTS `tableA` (
  `id` int(6) unsigned NOT NULL,
  `date` date NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `tableA` (`id`, `date`, `content`) VALUES
  ('1', '2017-10-03', 'The earth is round.'),
  ('2', '2018-01-01', 'The earth is flat'),
  ('3', '2018-01-01', 'One hundred angels can dance on the head of a pin'),
  ('4', '2018-01-02', 'The earth is flat and rests on a bull\'s horn'),
  ('5', '2018-01-03', 'The earth is like a ball.');

CREATE TABLE IF NOT EXISTS `tableB` (
  `date` date NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`date`)
) DEFAULT CHARSET=utf8;
INSERT INTO `tableB` (`date`, `content`) VALUES
  ('2017-01-01', 'ONE'),
  ('2017-12-01', 'TWO'),
  ('2018-01-02', 'THREE'),
  ('2018-01-05', 'FOUR');

Based on the this SQLFiddle, I'm looking for the following result.

tableA.id | tableB.content
--------------------------
1         | TWO
2         | THREE
3         | THREE
4         | THREE
5         | FOUR

Answer 1

Here is one solution:

SELECT a.id, b.content
FROM TableA a
JOIN TableB b ON b.date = (
  SELECT MIN(b2.date)
  FROM TableB b2
  WHERE b2.date >= a.date
);

I'm not sure whether this is the most efficient way, but it works.