I have two tables: the first one describes an item, the second one the history of the item. The history table can contain many events linked to an item. Example data set:
ItemID;Date;Version
1;09-14-01;1.0
1;09-15-01;1.1
1;09-16-01;1.2
etc.
What I want to do is create a page with a listing of all the items and also display the latest version number related to the item. So, I need get the latest version number related to the item (could be done using 'ORDER BY Date DESC') from the history table and link it to the item.
I know I could load the data into PHP arrays and do the merging of the data that way, but it just seems like an 'ugly' way of doing it. (no, I'm not saying PHP is ugly.
it's not.) Any ideas on how I would go about creating a query that would do this already on the MySQL side?
Thanks.
ItemID;Date;Version
1;09-14-01;1.0
1;09-15-01;1.1
1;09-16-01;1.2
etc.
What I want to do is create a page with a listing of all the items and also display the latest version number related to the item. So, I need get the latest version number related to the item (could be done using 'ORDER BY Date DESC') from the history table and link it to the item.
I know I could load the data into PHP arrays and do the merging of the data that way, but it just seems like an 'ugly' way of doing it. (no, I'm not saying PHP is ugly.
it's not.) Any ideas on how I would go about creating a query that would do this already on the MySQL side? Thanks.