Join tables on nearest date in the past, in MySQL

Question

I have a sqlite query that I'm trying to write. I have two tables:

TableA (sales): id sales date

TableB (goals): id goal date

I'm selecting from TableA like this: SELECT id,sales,date FROM TableA

Now to the "tricky" part. I need to join TableB to the query because I need the goal field in TableB for each row in TableA. TableB only contains goals for some dates, while TableA contains all dates. So I can't just use TableA.date = TableB.date

Instead, for each row in TableA I need to take the goal from TableB on the date nearest in the past to the date in TableA. Hope I was able to explain what I needed. Can't figure out how to do it..

Answer 1

SELECT a.id, a.sales, a.date, (SELECT TOP 1 Goal 
                               FROM TableB b WHERE b.date < a.date
                               ORDER BY b.date DESC) As Goal
FROM TableA a

Going off the nearest date in the past.