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
