Re: Re: Help with JOIN query

Yes, I'm totally with you Graham and I have created your tables and added some test data (you saw it in a previous post).. But I can't seem to come up with any solution for you, at least with MySQL 4.0.x. So if no one else has any brilliant solutions for this I think you'll need to stick with your solution to make the request in two steps, using PHP and an array to sort and put everyting together. Now, hopefully, you don't have hundreds of kilos of rows in your tables.. :) There might be solutions for this problem with never versions of MySQL, like using subqueries combined with joins like someone mentioned, but I'm not sure. I however, have never tested (or even needed to test) something like that, so I can't help you with that. If it is not important to keep the history of actions for each product you could simply update TableB, but I'll guess that's not the case - otherwise you wouldn't probably been asking us.. :) Another solution would be to move the previous action to an other table, like TableC for instance... Then TableC would be your history, TableB would only have your latest action.. But then again, we would have a one-to-one relation which basically means that you don't need two tables anymore. But you could at least consider this, if you are able to alter the database design a bit. Greetings, Krister Karlström, Helsinki Graham Cossey wrote:

Thank you for your input Jonathan but it's not quite what I need. I need the latest action from TableB (if it exists) determined by the record_id matching TableA and where there are more than one matching record in TableB select the one with the latest timestamp. As an over-simplified example of what I'm trying to achieve : TableA record_id product_ref

1             product1
2             product2

TableB timestamp record_id action

20080301     1            start
20080302     1            middle
20080301     2            start
20080302     2            middle
20080303     2            end

What I need returned is : 1,product1,middle 2,product2,end ----------- Graham