SQL : how to check if all entries of a table are in another table-CodePudding

I am trying to make a program where users enter what ingredients they have in their home, and the system gives recipes that they can make with using only those ingredients.

Table of ingredients that are needed to make recipes. 'recipe_ingredients'

 ---------------- --------------- ----------------- 
| curr_recipe_id | ingredient_id | ingredient_name |
 ---------------- --------------- ----------------- 
|              1 |             1 | ingredient 1    |
|              1 |             2 | ingredient 2    |
|              1 |             3 | ingredient 3    |
|              1 |             4 | ingredient 4    |
|              2 |             5 | ingredient 5    |
|              2 |             6 | ingredient 6    |
|              2 |             7 | ingredient 7    |
|              2 |             8 | ingredient 4    |
|              2 |             9 | ingredient 2    |
|              2 |            10 | ingredient 8    |
|              2 |            11 | ingredient 9    |
|              2 |            12 | ingredient 10   |
 ---------------- --------------- -----------------

Table of recipes

 ----------- ---------------------- 
| recipe_id | name                 |
 ----------- ---------------------- 
|         1 | recipe 1             |
|         2 | recipe 2             |
 ----------- ----------------------

Table of ingredients at home: 'home_ing'

 --------------- 
| home_ing_name |
 --------------- 
| ingredient 1  |
| ingredient 2  |
| ingredient 3  |
| ingredient 4  |
 ---------------

I want to get all the recipes that I can make using the ingredients that I have in home.

What I should get as output:

Table of possible recipes with ingredients at home:

 ---------------- 
| recipe id      |
 ---------------- 
|              1 |
 ----------------

I have tried using this query:

select * 
from recipes
where recipe_id in (select curr_recipe_id 
                    from recipe_ingredients 
                    where ingredient_name) in (select home_ing_name from home_ing));

This query returns:

 ---------------- 
| recipe id      |
 ---------------- 
|              1 |
|              2 |
 ----------------

because my ingredients at home table contains one of the ingredients of recipe 2.

CodePudding user response：

This query gets all the recipe_ingredient records which aren't met by the ingredients provided in the input, and from that gets all the recipes that aren't in that list.

SELECT r.recipe_id
FROM recipe r
WHERE NOT EXISTS(
   SELECT * FROM recipe_ingredient ri   
   WHERE ri.recipe_id = r.recipe_id
   AND NOT EXISTS(
       /* making the assumption here that the list of ingredients you have is provided
          in some form of input that can be placed in temp table or similar */
       SELECT * FROM #my_ingredients mi
       WHERE mi.ingredient_id = ri.ingredient_id
   )
)

The main difference here from the other approach is that it'll return recipes that don't have any ingredients, which depending on your requirements may or may not be desirable.

CodePudding user response：

You can:

Select recepi_id, MIN(HaveIngredient) from(
Select IR.recepi_id, Case when IH.home_ing_name is null then 0 else 1 end as HaveIngredient
from tableOfInredients IR
Left join tableOfIngredientsAtHome IH 
on IR.Ingredient_Name = IH.Home_ing_name) tmp
group by recepi_id
having MIN(HaveIngredient) = 1

First step here is I'm joining receipt list with ingredients I have. If I have ingredient I will mark as 1 if not 0.

Then I just want to have one row per recepi and I select recepi_Id with MIN value of HaveIngredient value. In having clause I select just the recepies where all ingredients is at home.

CodePudding user response：

select * from breakfast_recipes where 
           recipe_id in (
           select curr_recipe_id from breakfast_recipe_ingredients where
                   ingredient_name) in ('ingredient_1' AND 'ingredient_2' AND 'ingredient_3'));

I guess the solution is something related to that part. Not sure and could not solve :(