SQL Issue with trying to find specific values using HAVING-CodePudding

I've been trying to crack an sql problem, where I must display results of name and menu for a restaurant where everything is over £10. Tried several iterations and I believe using having is the solution, I haven't been able to return a value, instead I get warnings. Any ideas? Here is the ER diagram to relate to the problem:

SELECT DISTINCT
  restaurant.name AS 'restaurant',
  food_item.name AS 'item',
  food_item.price AS 'price'
FROM restaurant JOIN food_item
GROUP BY restaurant.name
HAVING min(food_item.price) > 10.00

CodePudding user response：

You are very close. Use this line in place of the one you have

FROM restaurant JOIN food_item ON restauraunt.id=food_item.restaurant_id

You need an ON condition in your JOIN clause, otherwise you'll get all possible combinations of restaurants and food items, which is both large and worthless.

CodePudding user response：

You use group by, Min() and Join:

select * 
from restaurant r
   inner join (
    select restaurant_id, min(price) minPrice
    from food_item
    group by restaurant_id) p on r.id = p.restaurant_id
where p.minPrice > 10;