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

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

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search