Recursive join to the same table using where clause from additional table-CodePudding

Question is related to Unlimited join to the same table until exists but brings an additional table to the query. Tables:

ingredients - contains ingredient IDs and names

ingredient_id|ingredient_name
1            |Water
2            |Salt
3            |Fancy Sauce
4            |Spices
5            |Pepper
6            |Chili

ingredients_to_ingredients - contains optional sub-ingredients for each ingredient

ingredient_id|mapped_ingredient_id
3            |1
3            |2
3            |4
4            |5
4            |6

meals_to_ingredients - contains ingredients (which may contain sub-ingredients) for each meal

meal_id|mapped_ingredient_id
1      |1
2      |2
3      |4

To get sub-ingredients (if any) for specified ingredient (let's say #3 - Fancy Sauce), I use:

WITH RECURSIVE
cte AS ( SELECT *
         FROM ingredients_to_ingredients 
         WHERE ingredient_id = 3
       UNION ALL
         SELECT ingredients_to_ingredients.*
         FROM ingredients_to_ingredients
         JOIN cte ON cte.mapped_ingredient_id = ingredients_to_ingredients.ingredient_id )
SELECT ingredients.ingredient_name
FROM cte
JOIN ingredients ON cte.mapped_ingredient_id = ingredients.ingredient_id;

And get the list:

Water
Salt
Spices
Pepper
Chili

Working demo - https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=0303d2ae8cded77053d87a0f31d9c374

Often I need to fetch sub-ingredients of multiple ingredients stored on other table - meals_to_ingredients. After a day of re-writing query, I was able to achieve this:

Query works indefinitely
Query returns errors
Query returns 0 records
Query returns absolutely all records

How do I rewrite query so it returns ingredients (and sub-ingredients) of let's say two random meals? I can post links to my non-working examples of modified query at DB Fiddle if needed.

Edit: Updated with desired output. Let's say new query randomly selects meals #1 and #3.

Meal #1 has mapped ingredients #1, #2 (meals_to_ingredients table), and these ingredients have no sub-ingredients (no records in ingredients_to_ingredients table)

Meal #3 has mapped ingredient #3 (meals_to_ingredients table), and this ingredient has sub-ingredients #1, #2, #4 (ingredients_to_ingredients table), while sub-ingredient #4 itself has further sub-ingredients #5, #6 (ingredients_to_ingredients table again).

So the result of all ingredients should be:

Water (ingredient_id - 1)
Salt (ingredient_id - 2)
Fancy Sauce (ingredient_id - 3)
Water (ingredient_id - 1)
Salt (ingredient_id - 2)
Spices (ingredient_id - 4)
Pepper (ingredient_id - 5)
Chili (ingredient_id - 6)

In fact, I will only need a unique list of ingredients, but I have no problems writing a query which filters duplicated records, so the example result above includes all the ingredients for easier understanding how query should work.

CodePudding user response：

Look at this:

WITH RECURSIVE
cte AS ( 
    SELECT meal_id, mapped_ingredient_id ingredient_id
    FROM meals_to_ingredients
  UNION ALL
    SELECT cte.meal_id, iti.mapped_ingredient_id
    FROM cte
    JOIN ingredients_to_ingredients iti USING (ingredient_id)
)
SELECT cte.meal_id, ingredient_id, i.ingredient_name
FROM cte
JOIN ingredients i USING (ingredient_id)
-- WHERE cte.meal_id IN (1, 3)
ORDER BY 1,2

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=ee26da089c9bb2e5046fea0bc29041ff