How to exclude all rows from query where is a single item that costs less than 10-CodePudding

For my assessment I have to obtain such result from mysql database:

 --------------------------------------- --------------------------------------- ------- 
| name                                  | name                                  | item  |
 --------------------------------------- --------------------------------------- ------- 
| Krispy Kreme - Edinburgh Lothian Road | 6 Assorted Doughnuts                  | 12.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed Dozen                 | 14.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed Double Dozen          | 23.95 |
| Krispy Kreme - Edinburgh Lothian Road | Sharer Dozen                          | 17.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed & Sharer Double Dozen | 24.95 |
| Krispy Kreme - Edinburgh Lothian Road | Sharer Double Dozen                   | 27.95 |
 --------------------------------------- --------------------------------------- -------

Show the name and delivery menu item for the restaurant where everything costs more than £10.

that's the question.

I've tried this

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

but I receive all the items that cost more than 10, but I need to receive the answer mentioned above, without specifying the name. I have to exclude all the restaurants that has positions in their menu that cost less than 10.

ERD is attached

CodePudding user response：

If I understand you correct, you want to get all the prices from all the restaurants. But if a restaurant has items in their menu that costs less than 10, nothing from that restaurant will be returned.

In that case, you have to add a subquery like the example here. Be aware that it is untested, but it should give you an idea:

    SELECT
        restaurant.NAME AS "restaurant name",
        food_item.NAME AS "item name",
        food_item.price AS "item price" 
    FROM
        restaurant
        JOIN food_item ON restaurant.id = food_item.restaurant_id 
        AND food_item.price 
    WHERE
        NOT EXISTS (select * from restaurant AS sub JOIN food_item AS food_item_sub ON sub.id = food_item_sub.restaurant_id WHERE food_item_sub.price <= 10 and sub.id = restaurant.id)

CodePudding user response：

If you need to exclude a restaurant if any of its menu options cost 10 or less, this can be expressed as a not exists semi-join.

Also note how, below, providing some short meaningful aliases helps make the query more compact and readable:

select 
  r.name  as restaurant_name, 
  f.name  as item_name, 
  f.price as item_price
from restaurant r
join food_item f on f.restaurant_id = r.id
where not exists (
  select * from food_item f
  where f.restaurant_id = r.id and f.item_price <= 10
);