How do I limit the result of a subquery in MySQL?

Question

Is there a way of limiting the result of a subquery? The sort of thing I'm trying to achieve can be explained by the query below:

SELECT *
FROM product p
JOIN (
    SELECT price
    FROM supplierPrices sp
    ORDER BY price ASC
    LIMIT 1
) ON (p.product_id = sp.product_id)

The idea would be to get only the lowest price for a particular product from a table that had all the price data in it. LIMIT 1 is limiting the entire result set, whereas excluding it would result in a row being returned for each price, with duplicated product data. I tried GROUP BY price as well to no avail.

Once the limit is working I need to apply IFNULL as well, so that if there is no price found at all for any supplier it can return a supplied string, such as "n/a" rather than NULL. I assume that would just mean modifying the SELECT as below, and changing the JOIN to a LEFT JOIN?

SELECT *, IFNULL(price,'n/a')

Answer 1

Did you try with min(price), but I'm not sure if this works...

Answer 2

Just to expand on Wolfy's answer slightly, and bearing in mind this is untested:

SELECT *
FROM product p
LEFT JOIN (
    SELECT product_id, MIN(price)
    FROM supplierPrices sp
    GROUP BY product_id
) x ON (p.product_id = x.product_id)

And, as you say, it should just be a matter of doing an IFNULL on that column to replace it with something sensible.