Connection problem with highest value in mysql table

Question

I have a product table ...

alt text http://img357.imageshack.us/img357/6393/productscx5.gif

and a revision table that should track changes to product information

alt text http://img124.imageshack.us/img124/1139/revisionslz5.gif

I am trying to query the database for all products with their latest version ...

select
*
from `products` as `p`
left join `revisions` as `r` on `r`.`product_id` = `p`.`product_id`
group by `p`.`product_id`
order by `r`.`modified` desc

but I always get the first revision. I need to do this with one (i.e. no subqueries). I can manage it in mssql, is this possible in mysql?

Answer 1

Here is how I would do it:

SELECT p.*, r.*
FROM products AS p
  JOIN revisions AS r USING (product_id)
  LEFT OUTER JOIN revisions AS r2 
    ON (r.product_id = r2.product_id AND r.modified < r2.modified)
WHERE r2.revision_id IS NULL;

In other words: find a version for which there is no other version with the same product_id and a large modified value.

Answer 2

. ( )

.

Answer 3

The same query can be analyzed in MySQL.

Why are you using left JOIN instead of INNER join or RIGHT join?

Also, if you want to go about it differently, you have the MAX function at your disposal.