You can try adding a quick test to the ON statement...
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.record_id = TableB.record_id
AND TableB.timestamp = MAX(TableB.timestamp)
Now, I haven't tested it.
I can only say the theory of it is accurate.
- Jon L.
On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey <graham.cossey@xxxxxxxxx>
wrote:
> I can't see how to accomplish what I need so if anyone has any
> suggestions they would be gratefully received...
>
> I'm using mysql 4.0.20 by the way.
>
> I have two tables :
>
> TableA
> record_id
> product_ref
>
> TableB
> timestamp
> record_id
> action
>
> I want to create a SELECT that joins these 2 tables where the JOIN to
> TableB only returns the most recent entry by timestamp.
>
> At present (using PHP) I do a SELECT on TableA then for each record
> returned I perform a 2nd SELECT something like :
>
> "SELECT timestamp, action FROM TableB WHERE record_id = '$record_id'
> ORDER BY timestamp DESC LIMIT 1"
>
> I now want to do it with one query to enable sorting the results by
> 'action' from TableB.
>
> Any suggestions?
>
> Hopefully I've made sense, if not I'll happily try and explain further
> on request.
>
> --
> Graham
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
