exchange rate

DROP TABLE IF EXISTS a;
CREATE TABLE IF NOT EXISTS a(
    id INT AUTO_INCREMENT PRIMARY KEY,
    trans_date DATE,
    currency CHAR(3)
);

INSERT INTO a(trans_date,currency)
VALUES
('2015-02-01','USD'),
('2015-04-04','USD'),
('2017-04-04','USD'),
('2015-01-01','JPY'),
('2015-04-04','JPY'),
('2017-04-04','JPY');

SELECT * FROM a;
+----+------------+----------+
| id | trans_date | currency |
+----+------------+----------+
|  1 | 2015-02-01 | USD      |
|  2 | 2015-04-04 | USD      |
|  3 | 2017-04-04 | USD      |
|  4 | 2015-01-01 | JPY      |
|  5 | 2015-04-04 | JPY      |
|  6 | 2017-04-04 | JPY      |
+----+------------+----------+

DROP TABLE IF EXISTS b;
CREATE TABLE IF NOT EXISTS b(
    currency CHAR(3),
    last_update DATE,
    price FLOAT
);

INSERT INTO b(currency,last_update,price)
VALUES
('USD','2015-01-01',13200.00),
('USD','2015-03-01',13160.00),
('USD','2017-04-04',13340.00),
('JPY','2015-01-01',110.00),
('JPY','2015-03-01',108.00),
('JPY','2017-04-04',112.00);

SELECT * FROM b;
+----------+-------------+-------+
| currency | last_update | price |
+----------+-------------+-------+
| USD      | 2015-01-01  | 13200 |
| USD      | 2015-03-01  | 13160 |
| USD      | 2017-04-04  | 13340 |
| JPY      | 2015-01-01  |   110 |
| JPY      | 2015-03-01  |   108 |
| JPY      | 2017-04-04  |   112 |
+----------+-------------+-------+

SELECT a.id,a.trans_date,MAX(b.last_update) AS last_update, a.currency
FROM a
JOIN b ON (a.trans_date>=b.last_update AND a.currency=b.currency)
GROUP BY a.id;
+----+------------+-------------+----------+
| id | trans_date | last_update | currency |
+----+------------+-------------+----------+
|  1 | 2015-02-01 | 2015-01-01  | USD      |
|  2 | 2015-04-04 | 2015-03-01  | USD      |
|  3 | 2017-04-04 | 2017-04-04  | USD      |
|  4 | 2015-01-01 | 2015-01-01  | JPY      |
|  5 | 2015-04-04 | 2015-03-01  | JPY      |
|  6 | 2017-04-04 | 2017-04-04  | JPY      |
+----+------------+-------------+----------+

SELECT a.id,a.trans_date,b.last_update, a.currency, b.price
FROM a
JOIN
(
SELECT a.id,MAX(b.last_update) AS last_update,b.currency
FROM a
JOIN b ON (a.trans_date>=b.last_update AND a.currency=b.currency)
GROUP BY a.id
) c USING(id)
JOIN b ON (b.last_update=c.last_update AND b.currency=c.currency);

+----+------------+-------------+----------+-------+
| id | trans_date | last_update | currency | price |
+----+------------+-------------+----------+-------+
|  1 | 2015-02-01 | 2015-01-01  | USD      | 13200 |
|  2 | 2015-04-04 | 2015-03-01  | USD      | 13160 |
|  3 | 2017-04-04 | 2017-04-04  | USD      | 13340 |
|  4 | 2015-01-01 | 2015-01-01  | JPY      |   110 |
|  5 | 2015-04-04 | 2015-03-01  | JPY      |   108 |
|  6 | 2017-04-04 | 2017-04-04  | JPY      |   112 |
+----+------------+-------------+----------+-------+