I think this is what you mean. You just want the timestamp and action from B in addition to something from A (I guessed product_ref), right? The MAX() function should take care of getting the latest timestamp.
explicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB JOIN TableA ON TableA.record_id = TableB.record_id ORDER BY TableB.action or if you want to join your tables implicitly in your WHERE clause, similar to what you had before, implicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB WHERE TableA.record_id = TableB.record_id ORDER BY TableB.action The problem with the implicit joins versus explicit joins is that you can't ever do OUTER JOINs, where you want many from one table and one (or many) from another table. For example if you want all sales reps and their sales, even if they don't have any. Implicit (or explicit INNER) JOINs will not show you all of the data. Jonathan Crawford [EMAIL PROTECTED] ---- Original message ---- >Date: 6 Mar 2008 18:46:18 -0000 >From: [EMAIL PROTECTED] >Subject: php-db Digest 6 Mar 2008 18:46:18 -0000 Issue 3990 >To: php-db@lists.php.net > > >php-db Digest 6 Mar 2008 18:46:18 -0000 Issue 3990 > >Topics (messages 44700 through 44700): > >Help with JOIN query > 44700 by: Graham Cossey > >Administrivia: > >To subscribe to the digest, e-mail: > [EMAIL PROTECTED] > >To unsubscribe from the digest, e-mail: > [EMAIL PROTECTED] > >To post to the list, e-mail: > php-db@lists.php.net > > >---------------------------------------------------------------------- >________________ >Date: Thu, 6 Mar 2008 18:46:14 +0000 >From: "Graham Cossey" <[EMAIL PROTECTED]> >Subject: Help with JOIN query >To: php-db@lists.php.net > >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
