Re: Re: Help with JOIN query

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