Get last state of item

Question

In MySQL, I have two tables with a 1:n relationship.

Table items has products, whose state is kept in another table, like so :

items:

id |ref_num|name    |...
1  |0001   |product1|...
2  |0002   |product2|...

items_states :

id|product_id|state_id|date
1 |1         |5       |2010-05-05 10:25:20
2 |1         |9       |2010-05-08 12:38:00
3 |1         |6       |2010-05-10 20:45:12
...

The states table is not relevant and only relates the state_id to the state name and so on.

How can I get products where the latest state is the one I specify, one item per row?

Thank you

Answer 1

You may want to try the following:

SELECT i.ref_num, i.name, s.latest_date
FROM   items i
JOIN   (
           SELECT   product_id, MAX(date) as latest_date
           FROM     items_states
           GROUP BY product_id
       ) s ON (s.product_id = i.id);

If you want to return just one item, simply add a WHERE i.id = ? to the query.

Test case:

CREATE TABLE items (id int, ref_num varchar(10), name varchar(10));
CREATE TABLE items_states (id int, product_id int, state_id int, date datetime);

INSERT INTO items VALUES (1, '0001', 'product1');
INSERT INTO items VALUES (2, '0002', 'product2');

INSERT INTO items_states VALUES (1, 1, 5, '2010-05-05 10:25:20');
INSERT INTO items_states VALUES (2, 1, 9, '2010-05-08 12:38:00');
INSERT INTO items_states VALUES (3, 1, 6, '2010-05-10 20:45:12');

Result:

+---------+----------+---------------------+
| ref_num | name     | latest_date         |
+---------+----------+---------------------+
| 0001    | product1 | 2010-05-10 20:45:12 |
+---------+----------+---------------------+
1 row in set (0.02 sec)

Answer 2

Either LEFT JOIN the items_states table to itself, requiring a second.date > first.date, and put a WHERE second.id IS NULL clause in it:

SELECT a.* 
FROM item_states a 
LEFT JOIN item_states b
ON a.product_id = b.product_id
AND b.product_id > a.product_id
WHERE b.id IS NULL AND a.state_id = <desired state>

Or make a row based query: see Mark Byers' example.