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

Reply via email to