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

SQL query on many to many relationship – Mysql

I have a recipes website, where recipes and foods are stored in MySQL db. There is a many to many relationship between tables.

Foods Table

FoodId FoodName
1 Spaghetti
2 Fish

Ingredients Table

IngredientId IngredientName
1 Lettuce
2 Milk
3 Pasta
4 Sauce

Foods-Ingredients Table

FoodId IngredientId
1 3
1 4
… …

The user want to get a list of foods he can make based on the ingredient he/she has. For example he has pasta and sauce.
So the input for the SQL Query will be a list :

[pasta, sauce]

The return should be also a string, in this case an array with one element :

[Spaghetti]

Is it possible to make this with 1 MySQL query and how?

Tags:

2

Answers


  1. 0

    I am making the following assumptions:

    1. You are using an undefined context (could be stored procedure, prepared statement, any MySQL context)
    2. You don’t mind executing multiple queries
    SELECT f.FoodName 
    FROM Foods f 
    JOIN Foods_Ingredients fi
        ON f.FoodId = fi.FoodId 
    JOIN Ingredients i
        ON fi.IngredientId = i.IngredientId 
    WHERE i.IngredientName IN ('Pasta', 'Sauce') 
    GROUP BY f.FoodId 
    HAVING COUNT(DISTINCT i.IngredientId) = 2

    If you are writing this as a stored procedure that can be applied to any set of ingredients, then you would replace ('Pasta', 'Sauce') with your data variable.

    Login or Signup to reply.
  2. 0

    You need to select not just foods that have the provided ingredients, but also foods that have only the provided ingredients.

    In my DB Fiddle https://www.db-fiddle.com/f/2sgrcERybJuZxFSrBco61Y/1

    I have added "Creamy Spaghetti" which requires Milk, Pasta, and Sauce. This gives us a test case where a recipe has both given ingredients, but requires other stuff.

    The fiddle includes a copy of @Viraj Shah’s answer (with corrected table names) to prove it doesn’t work in this case.

    Here’s my query:

    SELECT Foods.FoodName, FoodIngredients.FoodId, 
  COUNT(*) as Ingredients, 
  SUM(IF(IngredientName in ("pasta","sauce"), 1, 0)) as Available 
FROM FoodIngredients
  JOIN Foods ON (FoodIngredients.FoodId = Foods.FoodId)
  JOIN Ingredients ON (Ingredients.IngredientId = FoodIngredients.IngredientId)
GROUP BY Foods.FoodName, FoodIngredients.FoodId
HAVING Ingredients = Available;

    Here, I count all the ingredients the dishes have as well as how many of them match one of our available ingredients.

    I am making the assumption that there are no duplicate ingredients in the FoodIngredients table. That would throw off the count if there were duplicates. It would also be "wrong"; that field would be a foreign key in real life.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search