CODEWITHSUNDEEP
mysqljoin
Adam
Adam

Reputation: 723

Join MySQL on date range

I'm trying to query MySQL. Say I have 3 tables and imagine the data looked like this:

apples_sold
+--------+------------+----------+------+
| tranID | date       | apple_id | sold |
+--------+------------+----------+------+
|    101 | 2012-07-01 |      a01 |    2 |
|    102 | 2012-07-02 |      a01 |    5 |
|    103 | 2012-07-03 |      a01 |    1 |
|    104 | 2012-07-04 |      a01 |    0 |
|    105 | 2012-07-05 |      a01 |    2 |
+--------+------------+----------+------+

price_history
+---------+------------+----------+-------+
| priceID | date       | apple_id | price |
+---------+------------+----------+-------+
|     p01 | 2012-07-01 |      a01 | $0.25 |
|     p02 | 2012-07-03 |      a01 | $0.10 | <- price change
+---------+------------+----------+-------+

apple_name
+----------+----------+
| apple_id | name     |
+----------+----------+
|      a01 | McIntosh |
+----------+----------+

Trying to create a query that outputs this:

apple_prices
+------------+----------+-------+
| date       | name     | price |
+------------+----------+-------+
| 2012-07-01 | McIntosh | $0.25 |
| 2012-07-02 | McIntosh | $0.25 |
| 2012-07-03 | McIntosh | $0.10 | <- price change
| 2012-07-04 | McIntosh | $0.10 |
| 2012-07-05 | McIntosh | $0.10 |
+------------+----------+-------+

Didn't want to store the price with the apples_sold record, normalisation and all. The problem is im unsure the best way to join the sold record with the price.

Upvotes: 0

Views: 1441

Answers (1)

Zane Bien
Zane Bien

Reputation: 23135

You can use:

SELECT a.date, d.name, c.price
FROM apples_sold a
INNER JOIN
(
    SELECT a.date, MAX(b.date) AS maxdate
    FROM apples_sold a
    INNER JOIN price_history b ON a.date >= b.date
    GROUP BY a.date
) b ON a.date = b.date
INNER JOIN price_history c ON b.maxdate = c.date
INNER JOIN apple_name d ON a.apple_id = d.apple_id

SQLFiddle Demo

Upvotes: 5

Related Questions