Re: Help with JOIN query

I may be a little confused: the desire is to return all the rows from TableA that match the record_id of a row in TableB that has the MAX timestamp? If so, why not something like: SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id && timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; I'm guessing I'm confused, that it's something more complicated you're looking for. Jeff Krister Karlström wrote:

This will give you: ERROR 1111: Invalid use of group function It seems like the use of an aggregate (or how is it spelled?) function is not allowed in a join statement... /Krister Jon L. wrote:

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@gmail.com> 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