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