开发者

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 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜