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:

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?

CodePudding user response：

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)

CodePudding user response：

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);