CODEWITHSUNDEEP
mysqlselectmin
bland_dan
bland_dan

Reputation: 446

MySQL select statement with MIN

I have two tables in a MySQL database. Products and product items. Products contains fields product_id, product_name, image. Product item contains field item_id, product_id, size_id, doq_id, price.

There are many product items for one product.

How do I write a statement which returns the lowest price for each product?

I have the following:

SELECT p.product_name, p.image, p.product_id, MIN(pi.price) AS price
FROM (`product_items` as pi, `product` as p) 
WHERE `pi`.`product_id` = 'p.product_id' 
GROUP BY `p`.`product_name` 
ORDER BY RAND() 
LIMIT 3

But this is returning an empty data set.

Upvotes: 1

Views: 4123

Answers (1)

Marco
Marco

Reputation: 57573

Try this:

SELECT pr.id, pr.name, pr.image, MIN(pi.price) min_price
FROM products pr INNER JOIN product_items pi
ON pr.product_id = pi.product_id
GROUP BY pr.id

Upvotes: 1

Related Questions