I've got a items table...

alt text

along with a revisions table, which is designed to track changes to product info

alt text

I attempt to question the database for those items, using their newest revision...

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

however i always just obtain the first revision. I have to do that in one choose (ie no sub queries). I'm able to keep it in check in mssql, is even possible in mysql?

Here's how I'd get it done:

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;

Quite simply: discover the revision that not one other revision is available with similar product_id along with a greater modified value.

Begin and finish dates in your history table would make this a reality.(departing the newest finish date null and rubber stamping finish dates around the previous record while you place a replacement)

Otherwise you'll have to make use of a sub-query.

That same totally parsable in MySQL.

The reason for utilizing a Left JOIN rather than an INNER join or perhaps a RIGHT join?

Also if you wish to build a storage shed in different ways, you will find the MAX function available.