Question 21986 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Show the restaurant name and items where price is over a certain value

I have two tables:

restaurant
| id       | name     |
| -------- | -------- |
|          |          |

food_item

| restaurant_id | name     | price |
| --------      | -------- | ----- |
|               |          |       |
|               |          |       |

I am trying to get the restaurnat name, item name and price where all the restaurants’ items have a price higher than 10.

Examaple result:

restaurant item price
The King Fry item 1 12.30
THe King Fry item 2 13.00
The King Fry item 3 10.60

All the items listed on their menu are > 10

So far I have:

SELECT restaurant.name, food_item.name, food_item.price
FROM restaurant 
JOIN food_item ON restaurant.id = food_item.restaurant_id;
WHERE food_item.price > 10;

I managed to join the tables and show all the restaurants and its items where the price is > 10. However, I do not know how to display only the restaurant where all menu items have a value higher than 10. If there is a restaurnat with item values both higher and lower that 10 – do not show. How can I get the result?

Tags:

2

Answers


  1. 0

    you can check with NOT EXISTS of a restaurant has food cheaper than 10 whatever

    SELECT restaurant.name, food_item.name, food_item.price
FROM restaurant 
JOIN food_item ON restaurant.id = food_item.restaurant_id;
WHERE food_item.price > 10
AND NOt EXIST(SELECT 1 FROM food_item f1 WHERE price <= 10 AND f1.restaurant_id = food_item.restaurant_id)
    Login or Signup to reply.
  2. 0

    The issue is that you have two semicolons in your SELECT statement. The error is at the end of line 3.

    As the other answer indicated, we need to knock out all restuarants that have a cheap (i.e. <= $10.00) food item. We can do that with an NOT EXISTS. When those restaurants have been knocked out, the remaining restaurants are those that contain the expensive food items. There is actually no need to put a WHERE constraint on the outer SELECT. The inner SELECT has ensured that the list has been curated.

    SELECT restaurant.name, food_item.name, food_item.price
FROM restaurant 
JOIN food_item ON restaurant.id = food_item.restaurant_id
WHERE NOT EXISTS (
SELECT 1
FROM food_item cheap_food_item
WHERE restaurant.id = cheap_food_item.restaurant_id
AND cheap_food_item.price <= 10);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search