Skip to content
Advertisement

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

I have two tables:

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:

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?

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement