MySQL syntax group by restaurant with cheaper menu

Question

Hi everyone !

Here is my SQL request on MySQL (MySQL: 5.0.51a). I want have a list of restaurant with his cheaper menu:

select r.id, rm.id, rm.price, moyenne as note, 
get_distance_metres('47.2412254', '6.0255656', map_lat, map_lon) AS distance 

from restaurant_restaurant r 
LEFT JOIN restaurant_menu rm ON r.id = rm.restaurant_id 

where r.isVisible = 1 

group by r.id 
having distance < 2000 
order by distance ASC 
limit 0, 10

If I don't use group by, I have a list of all my menu and restaurant but when I use it, looks like he choose randomly a menu for my restaurant.

Thx for your help.

Answer 1

And something like that doesn't work :

SELECT r.id as restaurant_id, rm.id as menu_id, rm.price as price, r.moyenne AS note, 
get_distance_metres('47.2412254', '6.0255656', map_lat, map_lon) AS distance 

FROM restaurant_restaurant r 
LEFT JOIN restaurant_menu rm ON r.id = rm.restaurant_id 

WHERE r.isVisible = 1 

GROUP BY r.id 
HAVING MIN(price) AND distance < 120000
ORDER BY price ASC, distance ASC 
LIMIT 0, 10

Answer 2

Yes, if you select a column which is not functionally dependent on the GROUP BY clause you will get a random(*) choice in MySQL. This is not ANSI standard SQL and other databases will give you an error.

Do you want all restaurants that have a menu with a cheaper price than a certain level? If so that's easy, just add a

WHERE rm.price<1000   -- or whatever price

clause.

However if what you mean is you want to list restaurants with their lowest-price menu, what you are after is called a groupwise minimum and is surprisingly tricky to do in SQL. There are quite a few approaches to attack this; see also this question. Here is one with an outer-self-null-join:

SELECT
    r.id, rm.id, rm.price, r.moyenne AS note,
    get_distance_metres('47.2412254', '6.0255656', map_lat, map_lon) AS distance 
FROM restaurant_restaurant AS r
JOIN restaurant_menu rm ON r.id=rm.restaurant_id
LEFT JOIN restaurant_menu AS no_menu ON r.id=no_menu.restaurant_id AND no_menu.price<rm.price
WHERE no_menu IS NULL
AND r.isVisible=1
AND distance<2000
AND rm.price<1000   -- if you only want restaurants with a menu cheaper than certain price
ORDER BY distance
LIMIT 10;

Note that like many variants of the groupwise maximum this will give you two results for a single restaurant if it has two same-priced cheapest menus.

(*: actually you tend to get the first row in MySQL storage order, which a lot of the time will be the first one you put in the database, which is a bit of a trap because sometimes that's what you want so it looks like it's working, until the order changes for some reason and everything goes wrong. Danger!)