On Fri, Mar 7, 2008 at 12:27 AM, Jonathan Crawford <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] >
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
