Product table, options table. Select all products with their cheapest option?

[zq29]

Confusing title? I couldn't explain it any other way in such a small field! Ok, here goes...

 

product

id, name, description

 

product_option

id, product, name, price

 

I'm trying to display a list of products, each with its cheapest option as the display price.

 

SELECT p.*, po.`price` 
FROM `product` as p
LEFT JOIN `product_option` AS po ON p.`id` = po.`product`
ORDER BY po.`price`

 

This query returns duplicate data, one almost identical row per option, with the price differing. To be expected. I'm having difficulty only returning one row per product, with the price of the cheapest option. I tried adding a GROUP BY p.`id`, and this cut it down to one row per product, but it was not displaying the cheapest price, it displayed the price with the lowest id in the product_option table. Ordering before Grouping returns errors...

 

Am I attacking the task from the wrong direction, can someone point me in the right way? Many thanks.

[fenway]

There are a variety of ways to do this... all of them require you to find the uid of the po record with the lowest price *first*, and then join them back.

 

SELECT p.*, po.cheapest
FROM product AS p
LEFT JOIN 
( SELECT product, MIN(price) AS cheapest FROM product_options GROUP BY product ) AS po
ON ( po.product = p.id )

[aschk]

edit: darn it fenway I was about to post an identical query...

[fenway]

edit: darn it fenway I was about to post an identical query...

Ha... can't up your post count ;-)

 

I've seen other versions with HAVING, etc.... but this is the cleanest, IMHO.

[zq29]

Spot on! Thanks for the prompt solution fenway - Great stuff.

[fenway]

You may also want to have a look at this... I'll probably add this to the stickies at some point.

[zq29]

You may also want to have a look at this... I'll probably add this to the stickies at some point.

Informative read, thanks for the link.