Two table recursive lookup without hierarchy, is this possible?

[I HAVE UPDATED THIS QUESTION WITH A NEW QUERY] This is the current design of the SQL Server database I am working with.

I have two tables:

1. recipes

2. recipe ingredients

Recipes consist of recipe ingredients but an ingredient can be another recipe. In theory there are infinite levels as each recipe can have another ingredient that is also a recipe.

In the above data example, the Fresh Salsa recipe (ID 3047) has 4 ingredients. Three are raw Materials but one is another recipe (Recipe ID 3008). This recipe ID references another recipe in the 'recipes' table.

There is no hierarchy and I don't think I can create a hierarchy.

The goal is to extract all the recipe items for a particular recipe that have a 'sub' recipes and 'sub-sub' recipes etc.

It would seem like a recursive lookup would be the answer but because there is no hierarchy, this doesn't seem to work.

Here's my attempted query (the recipeItem list variable is a list of all the recipeitems that are also recipes created in a previous query):

<cfquery name="whatever">    WITH MenuPrepOfPreps (recipe_id, depth, otherRecipe_id, recipe_name)  AS    (      SELECT r.recipe_id,       0 as depth,      ri.otherRecipe_id,      r.recipe_name       FROM menu_recipes r      JOIN menu_recipeItems ri          ON ri.otherRecipe_id = r.recipe_id      WHERE ri.otherRecipe_id in (#recipeItemList#)      UNION ALL  -- recursive members      SELECT       mop.recipe_id,      mop.depth + 1 as depth,      ri.otherRecipe_id,      r.recipe_name      FROM menu_recipes r      JOIN menu_recipeItems ri          ON ri.otherRecipe_id = r.recipe_id      INNER JOIN MenuPrepOfPreps AS MOP          ON ri.otherRecipe_id = MOP.recipe_id            )    SELECT top(6)recipe_id, recipe_name  FROM MenuPrepOfPreps  GROUP BY recipe_id, recipe_name    </cfquery>  

It keeps creating an infinite loop. When I limit the results to the first few rows (top 6), it does give the desired result.

It is possible that the design of the database is not correct so this might never work.

Any help is appreciated.

https://stackoverflow.com/questions/66082516/two-table-recursive-lookup-without-hierarchy-is-this-possible February 07, 2021 at 06:13AM