MySQL LEFT JOIN, GROUP BY and ORDER BY not working as required

Question

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.

Answer 1

You need to set aliases properly I think and also set what you are joining on:

SELECT p.*, pp.price 
FROM products AS p 
LEFT JOIN product_price AS pp
ON pp.product_id = p.product_id 
GROUP BY p.product_id 
ORDER BY pp.date_updated DESC