CODEWITHSUNDEEP
phpmysqlsqlmagento
zbora23
zbora23

Reputation: 151

MySQL Query Products have multiple sizes and prices

I have following problem. I have 2 tables 'products' and 'productprices'. Each product has multiple prices i.e. ProductID 1 , SizeID 1, Price 4.00 | ProductID 1 , SizeID 2 , Price 5.50.

This is my current query...

SELECT  products.*, 
        productprices.Price 
FROM  products 
INNER JOIN productprices ON products.ID = productprices.ProductID 
WHERE products.Category='" . $categoryName . "'"

But I only want to return ONE record with lowest price for each product.

Can someone help ?

Thanks in advance.

Upvotes: 2

Views: 1646

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

This can be done with a self join productprices on the minimum of your product price

SELECT  p.*, 
        pp.*
FROM  products p
INNER JOIN productprices pp ON p.ID = pp.ProductID 
JOIN 
( SELECT ProductID ,MIN(price) price
 FROM productprices GROUP BY ProductID ) pp1
ON(pp.ProductID =pp1.ProductID AND pp.price = pp1.price)
WHERE p.Category='" . $categoryName . "'"

or if you don't need the whole row from productprices which has minimum price you can simply use MIN() function on your current query

SELECT  p.*, 
        MIN(pp.Price) price 
FROM  products p
INNER JOIN productprices pp ON p.ID = pp.ProductID 
WHERE p.Category='" . $categoryName . "'"
GROUP BY p.ID

Upvotes: 1

Related Questions