Sql query join nearest date

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

I'm guessing your using date to join the table so...

SELECT A.id, A.sales, A.date FROM TableA AS A ON INNER JOIN TableB AS B WHERE A.date = B.date

Hope this helps.

Answer 2

"Exactly equal date" would be a join on the date field. (As in Ash's answer)

"Nearest date" should probably read something like "nearest date in the future" or "nearest date in the past" (or maybe not). In this case you can use a subquery with a WHERE statement comparing the dates (< or >)

If "nearest date" is both in past and present, I'd probably code it by writing a stored procedure which creates a helper table containing the most relevant 'near date' (From B) for every date in table A. This way I'd have more control over the behaviour of the procedure and it'd be easier to change in the future.

Optimization for performance can always happen later.

Answer 3

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.

Answer 4

Just get the cartesian product and then filter out uninteresting rows. Basically:

select a.*, b.*
from a, b
where
not exists (
  select null
  from b b2
  where abs(b2.date - a.date) < abs(b.date - a.date)
)

I don't know if SQLite supports that, tough.