CODEWITHSUNDEEP
mysqlsqljoinsubquery
fgfjhgrjr erjhm
fgfjhgrjr erjhm

Reputation: 335

MySQL join between dates that are not equal

I want to join between two tables by dates. the first table has date for each day - Table a. The second table has few dates (not a date for each day) - Table b. I want to be able to join the two tables in a way that a.date=(the maximal date that is small or equal to a.date)

Is there a way to do that via MySQL?

Upvotes: 0

Views: 204

Answers (1)

Linger
Linger

Reputation: 15058

See SQL Fiddle for a possible solution:

SELECT A.Date AS ADate, A.Value AS AValue, 
(
 SELECT B.Date 
   FROM B 
   WHERE B.Date <= A.Date 
   ORDER BY B.Date DESC 
   LIMIT 1
) AS BDate,
(
 SELECT B.Value
   FROM B 
   WHERE B.Date <= A.Date 
   ORDER BY B.Date DESC 
   LIMIT 1
) AS BValue
FROM A 
WHERE A.Date = '2013-03-01'

In the above I am just using sub queries to pull the fields from table B.

Upvotes: 1

Related Questions