On Fri, Mar 7, 2008 at 12:27 AM, Jonathan Crawford <jcrawf02@xxxxxxxxx> wrote: > 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 > jcrawf02@xxxxxxxxx > 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
