MySQL LEFT JOIN, GROUP BY and ORDER BY not working as required
- by Simon
I have a table
'products' => ('product_id', 'name', 'description')
and a table
'product_price' => ('product_price_id', 'product_id', 'price', 'date_updated')
I want to perform a query something like
SELECT `p`.*, `pp`.`price`
FROM `products` `p`
LEFT JOIN `product_price` `pp` ON `pp`.`product_id` = `p`.`product_id`
GROUP BY `p`.`product_id`
ORDER BY `pp`.`date_updated` DESC
As you can probably guess the price changes often and I need to pull out the latest one. The trouble is I cannot work out how to order the LEFT JOINed table. I tried using some of the GROUP BY functions like MAX() but that would only pull out the column not the row.
Thanks.
