Selecting extra fields based on SQL MIN()

When using the SQL MIN() function, I want to select any additional fields that match the MIN row. Following query returns the correct MIN(sellPrice) but doest not match the correct row in the product_price table. How can I achieve this?

SELECT p.*, MIN(pp.sellPrice) AS sellPrice, pp.* FROM `product` AS p
LEFT JOIN `product_price_group` AS ppg ON p.`id` = ppg.`productId`
LEFT JOIN `product_pric开发者_如何转开发e` AS pp ON ppg.`id` = pp.`priceGroupId`
WHERE p.`active` = 1 AND p.id = 1

What you're looking for are all the rows where there ISN'T a lower price:

SELECT p.*, pp.sellPrice AS sellPrice, pp.* FROM `product` AS p
LEFT JOIN `product_price_group` AS ppg ON p.`id` = ppg.`productId`
LEFT JOIN `product_price` AS pp ON ppg.`id` = pp.`priceGroupId`
WHERE p.`active` = 1 AND p.id = 1
   AND NOT EXISTS (SELECT 1 FROM product_price AS pp2
       WHERE pp2.productId = pp.productId AND pp2.sellPrice < pp.sellPrice)
GROUP BY productId;

I'm guessing a little on what keys are involved. The idea is to have the WHERE NOT EXISTS look for any relevant rows with lower prices. This way MySQL will include in your results only rows where it couldn't find such a "lower price" match.

Do you want the minimum sellPrice for all products or for every product?

for all products

SELECT p.*
     , pp.sellPrice
     , pp.*
FROM product AS p
LEFT JOIN product_price_group AS ppg
  ON p.id = ppg.productId
LEFT JOIN product_price AS pp
  ON ppg.id = pp.priceGroupId
WHERE p.active = 1
  AND p.id = 1
  AND pp.sellPrice =
    ( SELECT MIN(pp.sellPrice)
      FROM product_price
    )

for every product

SELECT p.*
     , pp.sellPrice
     , pp.*
FROM product AS p
LEFT JOIN product_price_group AS ppg
  ON p.id = ppg.productId
LEFT JOIN product_price AS pp
  ON ppg.id = pp.priceGroupId
JOIN
  ( SELECT priceGroupId
         , pp.MIN(pp.sellPrice) AS sellPrice
    FROM product_price
    GROUP BY priceGroupId
  ) AS ppmin
  ON  ppmin.priceGroupId = pp.priceGroupId
  AND ppmin.sellPrice = sellPrice
WHERE p.active = 1
  AND p.id = 1

By some suggestions I used the GROUP BY attribute and following query is working correctly if I want the lowest sellPrice for a certain product:

SELECT p.*, MIN(pp.sellPrice) AS sellPrice, pp.quantity, pp.`priceGroupId`
FROM `product` AS p
LEFT JOIN `product_price_group` AS ppg ON ppg.productId = p.`id`
LEFT JOIN `product_price` AS pp ON pp.priceGroupId = ppg.`id`
WHERE p.`active` = 1 AND  p.id = 1 AND pp.sellPrice IS NOT NULL
GROUP BY pp.id;

For all products:

SELECT p.*, MIN(pp.sellPrice) AS sellPrice, pp.quantity, pp.`priceGroupId`
FROM `product` AS p
LEFT JOIN `product_price_group` AS ppg ON ppg.productId = p.`id`
LEFT JOIN `product_price` AS pp ON pp.priceGroupId = ppg.`id`
WHERE p.`active` = 1 AND pp.sellPrice IS NOT NULL
GROUP BY pp.id;