MySQL Left Join + Min
Seemingly simple MySQL question, but I've never had to do this before..
I have two tables, items and prices, with a one-to-many relationship.
Items Table
id, name
Prices Table
id, item_id, price
Where
prices.item_id = items.id
What I have so far:
SELECT items.id, items.name, MIN(prices.p开发者_开发问答rice)
FROM items
LEFT JOIN prices ON items.id = prices.item_id
GROUP BY items.id
How do I also return the corresponding prices.id for that minimum price? Thanks!
This will return multiple records for a record in Items if there are multiple Prices records for it with the minimum price:
select items.id, items.name, prices.price, prices.id
from items
left join prices on (
items.id = prices.item_id
and prices.price = (
select min(price)
from prices
where item_id = items.id
)
);
New, working answer, based on the final example in the MySQL 5.0 Reference Manual - 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column:
SELECT items.id, items.name, prices.price, prices.id
FROM items
LEFT JOIN prices
ON prices.item_id = items.id
LEFT JOIN prices AS filter
ON filter.item_id = prices.item_id
AND filter.price < prices.price
WHERE filter.id IS NULL
The LEFT JOIN
works on the basis that when prices.price
is at its minimum value, there is no filter.price
with a smaller value and the filter
rows values will be NULL.
Original incorrect answer:
SELECT items.id, items.name, prices.price, prices.id
FROM items
LEFT JOIN prices ON prices.item_id = items.id
ORDER BY prices.price ASC
LIMIT 1
Ok, how about?
SELECT items.id, items.name, MIN(prices.price), prices.id
FROM items
LEFT JOIN prices ON items.id = prices.item_id
GROUP BY items.id, MIN(prices.price)
SELECT top 1 items.id, items.name, prices.price, prices.id
FROM items
LEFT JOIN prices ON items.id = prices.item_id
ORDER BY prices.price ASC
精彩评论