MySQL restaurant syntax group with cheaper menu

Question

Here is my SQL query on MySQL (MySQL: 5.0.51a). I want to have a restaurant list with its 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 do not use a group, I have a list of all my menus and restaurant, but when I use it, it seems that he accidentally selects a menu for my restaurant.

Thanks for your help.

Answer 1

And something like this 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 that is functionally independent of the GROUP BY clause, you will get a random (*) choice in MySQL. This is not an ANSI SQL standard, and other databases will give you an error.

, , ? ,

WHERE rm.price<1000   -- or whatever price

.

, , , , , , SQL. ; . . -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;

, , , , .

(*: , , MySQL, , , , , , , - , . !)