How to write a query to find pizzas where all toppings are vegetarian

All we need is an easy explanation of the problem, so here it is.

I am new to building databases and want to create a query that finds all vegetarian pizzas based on the tables below. This would be pizzas where all toppings have a category of Vegetable in the pizza_toppings table. This is what I have but it is ugly. Is there a better way to write this or a better way to design the tables that would make the query easier?
Thank you.

mysql> select DISTINCT sel1.pizza_name
    -> from (select pizza_name,count(pizza_toppings.category) as no_meat
    ->         from pizza_development inner join pizza_toppings on
    ->         topping_name = pizza_toppings.name
    ->         where category != 'Meat'
    ->         group by pizza_name
    ->         having count(pizza_toppings.category)) sel1
    ->         INNER JOIN
    ->     (select pizza_name,count(pizza_toppings.category) as total
    ->         from pizza_development
    ->         INNER JOIN pizza_toppings on
    ->         topping_name = pizza_toppings.name
    ->         group by pizza_name
    ->         having count(pizza_toppings.category)) sel2
    ->         on sel1.pizza_name = sel2.pizza_name
    -> where no_meat = total;
+------------+
| pizza_name |
+------------+
| vegetarian |
+------------+
1 row in set (0.00 sec)

mysql>

FULL TABLES

mysql> select * from pizza_development;
+-------------+------------------+
| pizza_name  | topping_name     |
+-------------+------------------+
| new york    | thin crust       |
| new york    | Marinara sauce   |
| new york    | Mozzarella       |
| new york    | ham hock         |
| new york    | button mushrooms |
| vegetarian  | thin crust       |
| vegetarian  | Marinara sauce   |
| vegetarian  | Mozzarella       |
| vegetarian  | button mushrooms |
| vegetarian  | sweetcorn        |
| vegetarian  | bell peppers     |
| pepperoni   | thin crust       |
| pepperoni   | Marinara sauce   |
| pepperoni   | Mozzarella       |
| pepperoni   | Pepperoni        |
+-------------+------------------+
15 rows in set (0.00 sec)

mysql>
mysql> select * from pizza_toppings;
+------------------------+-----------+
| name                   | category  |
+------------------------+-----------+
| Swedish meatballs      | Meat      |
| Filet Mignon           | Meat      |
| Beef jerky             | Meat      |
| Steak                  | Meat      |
| Cod goujons            | Meat      |
| Roast beef             | Meat      |
| Korean beef            | Meat      |
| Taco beef              | Meat      |
| lamb kofta             | Meat      |
| Chicken Fajitas        | Meat      |
| Salami                 | Meat      |
| Pepperoni              | Meat      |
| sausage                | Meat      |
| meatballs              | Meat      |
| venison                | Meat      |
| Peking duck            | Meat      |
| Kobe beef              | Meat      |
| Chorizo                | Meat      |
| King prawns            | Meat      |
| grilled chicken        | Meat      |
| tuna                   | Meat      |
| chicken Tikka          | Meat      |
| curried chicken        | Meat      |
| Tandoori chicken       | Meat      |
| sweet and sour chicken | Meat      |
| sweet and sour pork    | Meat      |
| sardines               | Meat      |
| Mongolian beef         | Meat      |
| falafel                | Meat      |
| seared scallops        | Meat      |
| lobster tail           | Meat      |
| breaded oysters        | Meat      |
| crab meat              | Meat      |
| smoked salmon          | Meat      |
| pastrami               | Meat      |
| black pudding          | Meat      |
| blood sausage          | Meat      |
| Spam                   | Meat      |
| ham hock               | Meat      |
| Parma ham              | Meat      |
| pulled pork            | Meat      |
| Chilli con carne       | Meat      |
| bacon                  | Meat      |
| anchovies              | Meat      |
| soy chunks             | Meat      |
| Quorn chunks           | Meat      |
| Tofu cubes             | Meat      |
| roast turkey           | Meat      |
| satay chicken          | Meat      |
| Caviar                 | Meat      |
| cranberries            | Vegetable |
| Canadian bacon         | Vegetable |
| bell peppers           | Vegetable |
| baby spinach           | Vegetable |
| red onions             | Vegetable |
| French onions          | Vegetable |
| French fries           | Vegetable |
| red peppers            | Vegetable |
| sun-dried tomatoes     | Vegetable |
| sliced aubergine       | Vegetable |
| sliced courgettes      | Vegetable |
| carrot strips          | Vegetable |
| shallots               | Vegetable |
| artichoke hearts       | Vegetable |
| beetroot slices        | Vegetable |
| pine nuts              | Vegetable |
| pumpkin chunks         | Vegetable |
| raisins                | Vegetable |
| capers                 | Vegetable |
| chickpeas              | Vegetable |
| avocado                | Vegetable |
| jalapenos              | Vegetable |
| sweetcorn              | Vegetable |
| baby corn              | Vegetable |
| habanero chillis       | Vegetable |
| sauerkraut             | Vegetable |
| kimchi                 | Vegetable |
| pickled onions         | Vegetable |
| chopped garlic         | Vegetable |
| cherry tomatoes        | Vegetable |
| toasted almonds        | Vegetable |
| grilled apricots       | Vegetable |
| black olives           | Vegetable |
| green olives           | Vegetable |
| Shiitake mushrooms     | Vegetable |
| button mushrooms       | Vegetable |
| Portobello mushrooms   | Vegetable |
| porcini mushrooms      | Vegetable |
| wilted greens          | Vegetable |
| truffle shavings       | Vegetable |
| sliced green tomatoes  | Vegetable |
| pistachios             | Vegetable |
| orange segments        | Vegetable |
| roasted peanuts        | Vegetable |
| asparagus tips         | Vegetable |
| Feta cheese            | Cheese    |
| Gorgonzola             | Cheese    |
| Stilton                | Cheese    |
| Camembert              | Cheese    |
| Brie                   | Cheese    |
| Jarlsberg cheese       | Cheese    |
| Cheddar                | Cheese    |
| goat's cheese          | Cheese    |
| haloumi cheese         | Cheese    |
| smoked tofu            | Cheese    |
| bean curd              | Cheese    |
| Quark                  | Cheese    |
| Mozzarella             | Cheese    |
| Danish blue cheese     | Cheese    |
| Provolone              | Cheese    |
| Swiss cheese           | Cheese    |
| Red Leicester          | Cheese    |
| Wensleydale            | Cheese    |
| Port Salut cheese      | Cheese    |
| Monterey Jack          | Cheese    |
| Gruyere                | Cheese    |
| Edam                   | Cheese    |
| Gouda                  | Cheese    |
| Munster cheese         | Cheese    |
| soy cheese             | Cheese    |
| paneer cheese          | Cheese    |
| cottage cheese         | Cheese    |
| Ricotta cheese         | Cheese    |
| Parmesan cheese        | Cheese    |
| Manchego cheese        | Cheese    |
| Boursin cheese         | Cheese    |
| Queso Fresco           | Cheese    |
| Jalapeno Jack          | Cheese    |
| Marinara sauce         | Sauce     |
| barbecue sauce         | Sauce     |
| curry sauce            | Sauce     |
| Alfredo sauce          | Sauce     |
| salsa                  | Sauce     |
| ragu                   | Sauce     |
| olive tapenade         | Sauce     |
| tomato pesto           | Sauce     |
| basil pesto            | Sauce     |
| Carbonarra sauce       | Sauce     |
| Bolognese sauce        | Sauce     |
| Bechamel sauce         | Sauce     |
| hummus                 | Sauce     |
| harissa sauce          | Sauce     |
| Ranch sauce            | Sauce     |
| garlic aioli sauce     | Sauce     |
| pepper jelly           | Sauce     |
| Chimichurri sauce      | Sauce     |
| Soy-Miso sauce         | Sauce     |
| Hoisin sauce           | Sauce     |
| Thai Chili sauce       | Sauce     |
| fig jam                | Sauce     |
| satay sauce            | Sauce     |
| coconut curry sauce    | Sauce     |
| Zaatar and olive oil   | Sauce     |
| balsamic glaze         | Sauce     |
| Puttenesca sauce       | Sauce     |
| Piri Piri sauce        | Sauce     |
| flatbread              | Base      |
| gyro bread             | Base      |
| thin crust             | Base      |
| thick crust            | Base      |
| focaccia               | Base      |
| ciabatta               | Base      |
| puff pastry            | Base      |
| deep dish crust        | Base      |
| stuffed crust          | Base      |
| raised crust           | Base      |
| cracker crust          | Base      |
| unleavened crust       | Base      |
| wholewheat crust       | Base      |
| multigrain crust       | Base      |
| sourdough crust        | Base      |
| gluten-free crust      | Base      |
| polenta crust          | Base      |
| deep-fried base        | Base      |
+------------------------+-----------+
174 rows in set (0.00 sec)

mysql>

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

There are quite a few ways to slice this pie, so to speak, and there’s insufficient information about the overarching goal to state whether the current table design will do everything you’re trying to accomplish. That said, if you’re looking for a shorter query, you could do something like this:

SELECT tmp.`pizza_name`
  FROM (SELECT dev.`pizza_name`,
               COUNT(DISTINCT CASE WHEN tops.`category` = 'Meat' THEN tops.`name` ELSE NULL END) as `meat_toppings`
          FROM `pizza_development` dev INNER JOIN `pizza_toppings` tops ON dev.`topping_name` = tops.`name`
         GROUP BY dev.`pizza_name`) tmp 
 WHERE tmp.`meat_toppings` = 0;

This will return just the names of the pizzas that have the number of meat toppings. In an ideal world, 0 would mean the pizzas returned in the list could be considered vegetarian (though there is no consideration for the ingredients of the sauce or base). You can also use this to show "meat lover’s pizzas" with something like WHERE tmp.meat_toppings >= 5, or any number of different meat toppings.

If your goal is to develop an online ordering system where people can design their own pizzas, then you may want to look at having a table design that is a little more nuanced to take into account ingredients, whether something is kosher, halal, vegan, and the like.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply