Alex Rivera | Logout

MySQL Left Join + Min

Asked 2011-09-28T18:58:21.213
24

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.price)
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!

mysql group-by left-join greatest-n-per-group min
Edit
Report

2 Answers

38

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
    )
);
answered 2011-09-28T19:22:33.030
0
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
answered 2011-09-28T19:11:41.933

Your Answer