How do I query a query infinitely until a conditio

Question

In SQL Server

I have a database with a table called Recipe, this table contains recipes and the materials that go into the recipes. I need a query that shows all the base materials for a recipe. Unfortunately, some of these materials are actually other recipes within the recipes. FOR EXAMPLE: you have recipe AA01 and within that it makes up 2 base materials and recipe BB01. You must now look up the materials in that recipe to find the base materials in AA01. The trick is, you can have an infinite amount of recipes within recipes. This can be a ladder of searching. You don't know how far down the ladder you will have to look.

I came up with the idea of a query that searches and finds the next material and loops until it finally finds the base material. Sometimes it may have to loop once to find a material, other times it may have to loop 5 times to go down 5 levels.

The code below, unfortunately, I could not make it loop so it only finds the first layer. I could not make the query loop on itself.

SELECT 
    Recipe.RecipeID, 
    Recipe_1.RecipeID, 
    Recipe_1.MaterialID  
FROM Recipe 
LEFT JOIN Recipe AS Recipe_1 ON Recipe.MaterialID = Recipe_1.RecipeID
ORDER BY Recipe.RecipeID;

The solution would be to put this code in a loop or have it recursively call itself until it reaches a level where all base materials have been found. I have attached a picture of an example of a RECIPE table, what the code generates, and what I need it to generate (more levels) infinitely. You can see the highlighted rows are the ones missing from the actual one, those materials needed 2 levels to end up with the base materials. I have hard coded it to search up to 5 layers but obviously, there can be more. How can I have SQL loop and allocate the layers itself?

Is it possible to make a loop to continuously loop a query on itself?

Answer 1

This looks like it is producing the results you're looking for...

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#Recipe', 'U') IS NOT NULL 
DROP TABLE #Recipe;

CREATE TABLE #Recipe (
    Recipe VARCHAR(5) NOT NULL,
    Material VARCHAR(5) NOT NULL 
    );

INSERT #Recipe (Recipe, Material) VALUES 
    ('aa01', 'B1'),
    ('aa01', 'B2'),
    ('aa01', 'bb01'),
    ('bb01', 'B1'),
    ('bb01', 'cc01'),
    ('cc01', 'B3'),
    ('cc01', 'B4'),
    ('B1', 'B1'),
    ('B2', 'B2'),
    ('B3', 'B3'),
    ('B4', 'B4');

--SELECT * FROM #Recipe r;

--====================================================================================

IF OBJECT_ID('tempdb..#RecursiveOutput', 'U') IS NOT NULL 
DROP TABLE #RecursiveOutput;

WITH 
    cte_Recursion AS (  
        SELECT 
            r.Recipe,
            x = r.Material,
            Material = CAST(r.Material AS VARCHAR(8000)),
            NodeLevel = 1,
            MaterialLevel = CAST('m1.Material' AS VARCHAR(8000))
        FROM
            #Recipe r
        UNION ALL
        SELECT 
            cr.Recipe, 
            x = r.Material,
            Material = CAST(CONCAT(cr.Material, '>', r.Material) AS VARCHAR(8000)),
            NodeLevel = cr.NodeLevel + 1,
            MaterialLevel = CAST(CONCAT('m', cr.NodeLevel + 1, '.Material, ', cr.MaterialLevel) AS VARCHAR(8000))
        FROM 
            cte_Recursion cr
            JOIN #Recipe r
                ON cr.x = r.Recipe
        WHERE 1 = 1
            AND cr.Recipe <> r.Recipe
            AND r.Recipe <> r.Material
        )
SELECT 
    cr.Recipe, 
    cr.Material, 
    cr.NodeLevel,
    cr.MaterialLevel
    INTO #RecursiveOutput
FROM
    cte_Recursion cr;

-------------------------------------

DECLARE 
    @Split VARCHAR(8000) = '',
    @Material VARCHAR(8000) = '',
    @Level VARCHAR(8000) = '',
    @SelectList VARCHAR(8000) = '', 
    @MaxNode INT = 0,
    @DeBug BIT = 0;     -- set to 0 to execute & set to 1 to print...


SELECT
    @Split = CONCAT(@Split, '
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX(''>'', ro.Material, ', CASE WHEN ro.NodeLevel = 1 THEN '1' ELSE CONCAT('s', ro.NodeLevel - 1, '.Split + 1') END, '), 0)) ) s', ro.NodeLevel, ' (Split)'),
    @Material = CONCAT(@Material, '
    CROSS APPLY ( VALUES (SUBSTRING(ro.Material, ', CASE WHEN ro.NodeLevel = 1 THEN '1, ISNULL(s1.Split -1,' ELSE CONCAT('s', ro.NodeLevel - 1, '.Split + 1, ISNULL(s', ro.NodeLevel, '.Split - s', ro.NodeLevel - 1, '.Split - 1,') END, ' 1000))) ) m', ro.NodeLevel, ' (Material)'),
    @Level = CONCAT(@Level, CASE WHEN ro.NodeLevel = 1 THEN '' ELSE CONCAT('
    CROSS APPLY ( VALUES (CAST(COALESCE(', ro.MaterialLevel, ') AS VARCHAR(20))) ) L', ro.NodeLevel, ' ([Level ', ro.NodeLevel, ' Material])') END),
    @SelectList =  CONCAT(@SelectList, CASE WHEN ro.NodeLevel = 1 THEN '' ELSE CONCAT(',
    L', ro.NodeLevel, '.[Level ', ro.NodeLevel, ' Material]') END),
    @MaxNode = CASE WHEN ro.NodeLevel > @MaxNode THEN ro.NodeLevel ELSE @MaxNode END
FROM 
    #RecursiveOutput ro
GROUP BY 
    ro.NodeLevel,
    ro.MaterialLevel;

DECLARE @sql VARCHAR(MAX) = CONCAT('
SELECT DISTINCT
   ro.Recipe,
    [Level 1 Material] = CAST(m1.Material AS VARCHAR(20))', 
    @SelectList, '
FROM
    #RecursiveOutput ro', 
    @Split, 
    @Material,
    @Level, '
WHERE
    EXISTS (SELECT 1 FROM #Recipe r WHERE L', @MaxNode, '.[Level ', @MaxNode, ' Material] = r.Recipe AND r.Recipe = r.Material);')

IF @DeBug = 1
BEGIN 
    PRINT(@sql);
END;
ELSE 
BEGIN
    EXEC(@sql);
END; 

Results...

Recipe Level 1 Material     Level 2 Material     Level 3 Material
------ -------------------- -------------------- --------------------
aa01   B1                   B1                   B1
aa01   B2                   B2                   B2
aa01   bb01                 B1                   B1
aa01   bb01                 cc01                 B3
aa01   bb01                 cc01                 B4
B1     B1                   B1                   B1
B2     B2                   B2                   B2
B3     B3                   B3                   B3
B4     B4                   B4                   B4
bb01   B1                   B1                   B1
bb01   cc01                 B3                   B3
bb01   cc01                 B4                   B4
cc01   B3                   B3                   B3
cc01   B4                   B4                   B4

Edit: Below is the same solution as above but written to eliminate the use of the CONCAT function for earlier versions of SQL Server...

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#Recipe', 'U') IS NOT NULL 
DROP TABLE #Recipe;

CREATE TABLE #Recipe (
    Recipe VARCHAR(5) NOT NULL,
    Material VARCHAR(5) NOT NULL 
    );

INSERT #Recipe (Recipe, Material) VALUES 
    ('aa01', 'B1'),
    ('aa01', 'B2'),
    ('aa01', 'bb01'),
    ('bb01', 'B1'),
    ('bb01', 'cc01'),
    ('cc01', 'B3'),
    ('cc01', 'B4'),
    ('B1', 'B1'),
    ('B2', 'B2'),
    ('B3', 'B3'),
    ('B4', 'B4');

--SELECT * FROM #Recipe r;

--====================================================================================

IF OBJECT_ID('tempdb..#RecursiveOutput', 'U') IS NOT NULL 
DROP TABLE #RecursiveOutput;

WITH 
    cte_Recursion AS (  
        SELECT 
            r.Recipe,
            x = r.Material,
            Material = CAST(r.Material AS VARCHAR(8000)),
            NodeLevel = 1,
            MaterialLevel = CAST('m1.Material' AS VARCHAR(8000))
        FROM
            #Recipe r
        UNION ALL
        SELECT 
            cr.Recipe, 
            x = r.Material,
            Material = CAST(cr.Material + '>' + r.Material AS VARCHAR(8000)),
            NodeLevel = cr.NodeLevel + 1,
            MaterialLevel = CAST('m' + CAST(cr.NodeLevel + 1 AS VARCHAR(10)) + '.Material, ' + cr.MaterialLevel AS VARCHAR(8000))
        FROM 
            cte_Recursion cr
            JOIN #Recipe r
                ON cr.x = r.Recipe
        WHERE 1 = 1
            AND cr.Recipe <> r.Recipe
            AND r.Recipe <> r.Material
        )
SELECT 
    cr.Recipe, 
    cr.Material, 
    cr.NodeLevel,
    cr.MaterialLevel
    INTO #RecursiveOutput
FROM
    cte_Recursion cr;

-------------------------------------

DECLARE 
    @Split VARCHAR(8000) = '',
    @Material VARCHAR(8000) = '',
    @Level VARCHAR(8000) = '',
    @SelectList VARCHAR(8000) = '', 
    @MaxNode INT = 0,
    @DeBug BIT = 0;     -- set to 0 to execute & set to 1 to print...


SELECT
    @Split = @Split + '
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX(''>'', ro.Material, ' + CASE WHEN ro.NodeLevel = 1 THEN '1' ELSE 's' + CAST(ro.NodeLevel - 1 AS VARCHAR(10)) + '.Split + 1' END + '), 0)) ) s' + CAST(ro.NodeLevel AS VARCHAR(10)) + ' (Split)',

    @Material = @Material + '
    CROSS APPLY ( VALUES (SUBSTRING(ro.Material, ' + CASE WHEN ro.NodeLevel = 1 THEN '1, ISNULL(s1.Split -1,' ELSE 's' + CAST(ro.NodeLevel - 1 AS VARCHAR(10)) + '.Split + 1, ISNULL(s' + CAST(ro.NodeLevel AS VARCHAR(10)) + '.Split - s' 
            + CAST(ro.NodeLevel - 1 AS VARCHAR(10)) + '.Split - 1,' END + ' 1000))) ) m' + CAST(ro.NodeLevel as VARCHAR(10)) + ' (Material)',

    @Level = @Level + CASE WHEN ro.NodeLevel = 1 THEN '' ELSE '
    CROSS APPLY ( VALUES (CAST(COALESCE(' + ro.MaterialLevel + ') AS VARCHAR(20))) ) L' + CAST(ro.NodeLevel AS VARCHAR(10)) + ' ([Level ' + CAST(ro.NodeLevel as VARCHAR(10)) + ' Material])' END,

    @SelectList =  @SelectList + CASE WHEN CAST(ro.NodeLevel as VARCHAR(10)) = 1 THEN '' ELSE ',
    L' + CAST(ro.NodeLevel AS VARCHAR(10)) + '.[Level ' + CAST(ro.NodeLevel AS VARCHAR(10)) + ' Material]' END,

    @MaxNode = CASE WHEN ro.NodeLevel > @MaxNode THEN ro.NodeLevel ELSE @MaxNode END
FROM 
    #RecursiveOutput ro
GROUP BY 
    ro.NodeLevel,
    ro.MaterialLevel;

DECLARE @sql VARCHAR(MAX) = '
SELECT DISTINCT
   ro.Recipe,
    [Level 1 Material] = CAST(m1.Material AS VARCHAR(20))' +
    @SelectList + '
FROM
    #RecursiveOutput ro' +
    @Split +
    @Material +
    @Level + '
WHERE
    EXISTS (SELECT 1 FROM #Recipe r WHERE L' + CAST(@MaxNode AS VARCHAR(10)) + '.[Level ' + CAST(@MaxNode AS VARCHAR(10)) + ' Material] = r.Recipe AND r.Recipe = r.Material);'

IF @DeBug = 1
BEGIN 
    PRINT(@sql);
END;
ELSE 
BEGIN
    EXEC(@sql);
END;

HTH, Jason

Answer 2

Easy :)

1. Level = 1

2. Create table variable with ingredients of top-level recipe (AA01) and mark records with Level (in separate column of your table variable)

3. While count of obtained records was greater than zero

   1. Increase Level

   2. Fetch all ingredients which have parent recipe among the ingredients found in previous step (Level - 1) and add them to the temporary table variable.

   3. Loop

4. At this moment you have the result. Level in table also indicates depth of given ingredient.

This avoids recursion and is computationally very cheap.

Just make sure that you detect circular path during adding the ingredients in AFTER INSERT trigger (and fail adding if it was detected) so recipe BB01 in recipe AA01 does not have subrecipe AA01. But this is achievable by the above approach, too, so it is not a big problem.

This code should do that (not tested, please report problems, if any):

DECLARE @RecipeID nchar(100) = 'AA01'; -- input - recipe you are searching for

-- initializations
DECLARE @RecipeDepth int = 0;
DECLARE @IngredientsFound TABLE (
    RecipeID NCHAR(100),
    MaterialID NCHAR(100),
    RecipeDepth int);
DECLARE @MAX_RECIPEDEPTH int = 100; -- constant for failsafe purposes

-- 0th step
INSERT INTO @IngredientsFound
    SELECT RecipeID, MaterialID, @RecipeDepth
        FROM Recipe
        WHERE RecipeID = @RecipeID;

-- following steps
WHILE (@@ROWCOUNT > 0 AND @RecipeDepth <= @MAX_RECIPEDEPTH)
BEGIN
    SET @RecipeDepth += 1;
    INSERT INTO @IngredientsFound
        SELECT RecipeID, MaterialID, @RecipeDepth
            FROM Recipe
            WHERE RecipeID IN (SELECT MaterialID
                                    FROM @IngredientsFound
                                    WHERE RecipeDepth = @RecipeDepth - 1);
END;

-- result
SELECT RecipeID, MaterialID, RecipeDepth
    FROM @IngredientsFound;

• instead of Level I used RecipeDepth, because the former one seems to be a reserved word or keyowrd
• I added a safeguard set 100 levels deep to prevent endless loop when there is a circular reference
• I finished every command with ; because in the future they intended to make them mandatory