How do I limit the result of a subquery in MySQL?
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 tr开发者_如何学运维ied 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')
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.
精彩评论