Join Two Tables Where Table A Has A Date Value And Needs To Find The Next Date In B Below The Date In A

I got this table 'A': | id | date | =================== | 1 | 2010-01-13 | | 2 | 2011-04-19 | | 3 | 2011-05-07 | | .. | ... | and this table 'B': | date | va

Solution 1:

-- Create and fill first tableCREATETABLE `id_date` (
  `id` int(11) NOTNULL auto_increment,
  `iddate` dateNOTNULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERTINTO `id_date` VALUES(1, '2010-01-13');
INSERTINTO `id_date` VALUES(2, '2011-04-19');
INSERTINTO `id_date` VALUES(3, '2011-05-07');

-- Create and fill second table    CREATETABLE `date_val` (
  `mydate` dateNOTNULL,
  `myval` varchar(4) collate utf8_bin NOTNULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERTINTO `date_val` VALUES('2009-03-29', '0.5');
INSERTINTO `date_val` VALUES('2010-01-30', '0.55');
INSERTINTO `date_val` VALUES('2011-08-12', '0.67');

-- Get the result table as asked in questionSELECT iddate, t2.mydate, t2.myval
FROM `id_date` t1
JOIN date_val t2 ON t2.mydate <= t1.iddate
AND t2.mydate = ( 
SELECTMAX( t3.mydate ) 
FROM `date_val` t3
WHERE t3.mydate <= t1.iddate )

What we're doing:

• for each date in the id_date table (your table A),
• we find the date in the date_val table (your table B)
• which is the highest date in the date_val table (but still smaller than the id_date.date)

Solution 2:

You could use a subquery with limit 1 to look up the latest value in table B:

select  id
,       date
,       (
        select  value
        from    B
        where   B.date < A.dateorderby
                B.date desc
        limit   1
        ) as value
from    A

Solution 3:

I have been inspired by the other answers but ended with my own solution using common table expressions:

WITH datecombination (id, adate, bdate) AS 
(
SELECT id, A.date, MAX(B.Date) as Bdate
FROM tableA A
LEFT JOIN tableB B
ON B.date <= A.dateGROUPBY A.id, A.date
)
SELECT DC.id, DC.adate, B.value FROM datecombination DC
LEFT JOIN tableB B
ON DC.bdate = B.bdate 

Solution 4:

The INNER JOIN return rows when there is at least one match in both tables. Try this.

Select A.id,A.date,b.value 
from A inner join B 
on A.date=b.date