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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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

Method 3

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)

Method 4

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0