mysql - Get last state of item -

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

Products in table items, whose state is placed in another table, such as:

items:

  id | Ref_num | Name | ... 1 | 0001 | Product 1 | ... 2 | 0002 | Product 2 | ...  

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 table of states is not relevant and is related only to the name of the state in the name of the state and so on.

How can I get the product where I specify the latest state, one item per

You can try the following:

P>

  SELECT i.ref_num, i.name, s.latest_date SELECT product_id, MAX (date) latest_data item_state group by product_id) s on (sproduct_id = iid);  

If you want to return only one item, simply query a WHERE i.id =? Add .

Test case:

  Create table item (id int, ref_num varchar (10), name varchar (10)); Create table items / place (id int, product_id int, state_id int, date datetime); Insert item values ​​(1, '0001', 'product1'); Insert item values ​​(2, '0002', 'Product2'); Enter values ​​inside items VALUES (1, 1, 5, '2010-05-05 10:25:20'); Enter the values ​​of items VALUES (2, 1, 9, '2010-05-08 12:38:00'); Enter prices inside items (3, 1, 6, '2010-05-10 20:45:12');  

Result:

  + --------- + ---------- + ----- ---------------- + | Ref_num | Name | Latest_date | + --------- + ---------- + --------------------- + | 0001 | Product 1 | 2010-05-10 20:45:12 | + --------- + ---------- + --------------------- + 1 line set (0.02 seconds )