MySql JOIN on most recent start_date?
I have two tables, one with transactions (with date). The other with a percentage and date the percentage it went into effect (assume 00:00:00). The percentage remains in effect until a new percent goes into effect. I need to join on the percentage that was in effect when the transaction happened.
transactions_table event_date amount 2011-01-01 230 2011-02-18 194 2011-03-22 56 2011-04-30 874 percent_table effective percent 2010-12-30 15 2011-03-05 25 2011-04-12 30
The result I'm looking for is:
event_date amount percent 2011-01-01 230 15 2011-02-18 194 15 2011-03-22 56 25 2011-04-30 874 30
SELECT t.event_date, t.amount, p.percent FROM transactions_table AS t LEFT JOIN percent_table AS p ON t.event_date >= p.effective ORDER BY `t`.`event_date` DESC LIMIT 0 , 30;
That gives me, seemingly random percentages. It seems to me like I need to get the greatest date >= p.effective, not just any random date >= p.effective.
SELECT t.event_date, p.percent FROM bedic_sixsummits_transactions AS t LEFT JOIN bedic_sixsummits_percent AS p ON MAX(t.event_date >= p.effective) ORDER BY `t`.`event_date` DESC LIMIT 0 , 30
but MySQL just laughed at my feeble attempt.
How can I do this?
SELECT t.event_date, t.amount, p.percent FROM bedic_sixsummits_transactions AS t LEFT JOIN bedic_sixsummits_percent AS p ON p.effective = ( SELECT MAX( p2.effective ) FROM bedic_sixsummits_percent AS p2 WHERE p2.effective <= t.event_date ) ORDER BY t.event_date DESC LIMIT 0 , 30
Even more simpler and with no subquery:
SELECT event_date, amount, MAX(_percent) as _percent FROM transactions_table LEFT JOIN percent_table p1 ON event_date >= effective GROUP BY event_date, amount ORDER BY event_date;
Note that it is possible because of the business model involved. If you wan't to retrieve other fields of the percent_table it won't be appropriate anymore :/