MySQL Left Join + Min

greatest-n-per-group, group-by, left-join, min, mysql

Solution

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
    )
);

Problem

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!

Original source