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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to