gipadm gipadm - 2 months ago 8
MySQL Question

Subquery last date/value from different table

I am trying to append the last fruit count from table2 to a query on table1.

table1 table2
+--------+--------+-------+-----+ +--------+------+-------+
| fruit | season | carbs | fat | | fruit | date | count |
+--------+------+---------+-----+ +--------+------+-------+
| apple | summer | 21 | 0 | | apple | 2015 | 700 |
| banana | all yr | 27 | 0 | | apple | 2014 | 500 |
+--------+--------+-------+-----+ | banana | 2014 | 200 |
| banana | 2013 | 300 |
+--------+------+-------+

goal:
+--------+--------+-------+-----+------------+
| fruit | season | carbs | fat | last_count |
+--------+------+---------+-----+------------+
| apple | summer | 21 | 0 | 700 |
| banana | all yr | 27 | 0 | 200 |
+--------+--------+-------+-----+------------+


All I can come up with is something like this (not working):

SELECT t1.*, t2.count AS last_count FROM table1 AS t1
INNER JOIN
(SELECT fruit, count FROM table2 ORDER BY date DESC) t2 ON t1.fruit = t2.fruit


Also, I should be able to filter results based on values from table1 (e.g.
WHERE t1.carbs < 25
)

Answer

One way to solve this is to join table1 to table2 twice. The first join is to the full table2 which has the count, and the second join is to a subquery of table2 which include only records for the latest fruit, for each fruit.

SELECT t1.*, t2.count AS last_count
FROM table1 t1
INNER JOIN table2 t2
    ON t1.fruit = t2.fruit
INNER JOIN
(
    SELECT fruit, MAX(date) AS date
    FROM table2
    GROUP BY fruit
) t3
    ON t2.fruit = t3.fruit AND
       t2.date  = t3.date
Comments