CTE recursive query

Question

I am working on a system where I have a Recipe. A RecipeItem is what completing that recipe will yield. CraftMaterials are the components that must be combined to complete the recipe.

CREATE TABLE Recipe
(
    RecipeId    bigint
    ...other data
)

CREATE TABLE CraftMaterial
(
    CraftMaterialId    bigint,
    ItemId             bigint,
    RecipeId           bigint,
    Amount             int
)

CREATE TABLE RecipeItem
(
    RecipeItemId     bigint
    RecipeId         bigint,
    ItemId           bigint,
    Amount           int
)

CREATE TABLE Item
(
    ItemId      bigint
    Name        varchar(200)
    IconName    varchar(200)
)

When you do the join Recipe -> RecipeItem -> Item, you get hte name of the item the recipe makes. Recipe item stores how many of that item are created.

When you join Recipe -> CraftMaterial -> Item you get a list of the items that are combined to make this recipe.

A recipe's material(s) may be themselves a recipe.

What I am looking to do is use a CTE to get the materials at each level of construction until I am down to the base items - that is the item in CraftMaterial does not have a corresponding record in RecipeItem.

What I have so far gets the item's first tier recipe correctly. It is the recursive portion of the query that is giving me trouble.

DECLARE @RecipeId int

SET @RecipeId = 5951

;WITH cteMaterials (CCraftMatId, ItId, RecId, Amt, Name, Icon, MatLevel)
AS
(
    SELECT
        cm.CraftMaterialId,
        cm.ItemId,
        cm.RecipeId,
        cm.Amount,
        i.Name,
        i.IconFileName,
        1
    FROM CraftMaterial cm
    JOIN Item i ON cm.ItemId = i.ItemId
    WHERE cm.RecipeId = @RecipeId

    UNION ALL

    ???
)

select * from cteMaterials

Answer 1

Nice challenge, had a bit of a struggle with the relationships, they dont feel very natural to work with but I can see how they are used. Find below a working example or try it here

Data Setup

if (object_id('Recipe') is not null)
    drop table Recipe
if (object_id('RecipeItem') is not null)
    drop table RecipeItem
if (object_id('CraftMaterial') is not null)
    drop table CraftMaterial
if (object_id('Item') is not null)
    drop table Item

create table Recipe (RecipeId bigint)

create table CraftMaterial
(
    CraftMaterialId bigint identity(1, 1),
    ItemId bigint,
    RecipeId bigint,
    Amount int
)

create table RecipeItem
(
    RecipeItemId bigint identity(1, 1),
    RecipeId bigint,
    ItemId bigint,
    Amount int
)

create table Item
(
    ItemId bigint identity(1, 1),
    Name varchar(200),
    IconName varchar(200)
)

declare @id bigint = 0

insert  Recipe
        (RecipeId)
values
        (5951),
        (5952),
        (5953),
        (5954)

insert  Item
        (Name, IconName)
values
        ('Chocolate Cupcakes', 'cc_ico')
select
    @id = @@IDENTITY

insert  RecipeItem
        (RecipeId, ItemId, Amount)
values
        (5951, @id, 12)

insert  Item
        (Name, IconName)
values
        ('Flour', 'flour_ico')
select
    @id = @@IDENTITY

insert  CraftMaterial
        (ItemId, RecipeId, Amount)
values
        (@id, 5951, 1)

insert  Item
        (Name, IconName)
values
        ('chocolate', 'choc_ico')
select
    @id = @@IDENTITY

insert  RecipeItem
        (RecipeId, ItemId, Amount)
values
        (5952, @id, 2)

insert  CraftMaterial
        (ItemId, RecipeId, Amount)
values
        (@id, 5951, 1)

insert  Item
        (Name, IconName)
values
        ('milk', 'milk_ico')
select
    @id = @@IDENTITY

insert  CraftMaterial
        (ItemId, RecipeId, Amount)
values
        (@id, 5952, 300)

insert  RecipeItem
        (RecipeId, ItemId, Amount)
values
        (5953, @id, 1)

insert  Item
        (Name, IconName)
values
        ('cocao', 'cocao_ico')
select
    @id = @@IDENTITY

insert  CraftMaterial
        (ItemId, RecipeId, Amount)
values
        (@id, 5952, 75)

insert  RecipeItem
        (RecipeId, ItemId, Amount)
values
        (5954, @id, 1)

insert  Item
        (Name, IconName)
values
        ('cow', 'cow_ico')
select
    @id = @@IDENTITY


insert  CraftMaterial
        (ItemId, RecipeId, Amount)
values
        (@id, 5953, 1)

insert  Item
        (Name, IconName)
values
        ('cocao bean', 'cbean_ico')
select
    @id = @@IDENTITY

insert  CraftMaterial
        (ItemId, RecipeId, Amount)
values
        (@id, 5954, 250)

CTE Example

declare @RecipeId int

set @RecipeId = 5951;

with    cteMaterials(CCraftMatId, ItId, RecId, Amt, Name, Icon, ChildItem, MatLevel)
            as (
                select
                    cm.CraftMaterialId,
                    cm.ItemId,
                    cm.RecipeId,
                    cm.Amount,
                    i.Name,
                    i.IconName,
                    cm.ItemId ChildItem,
                    1 MatLevel
                from
                    RecipeItem as ri
                    inner join CraftMaterial as cm
                        on cm.RecipeId = ri.RecipeId
                    inner join Item as i
                        on cm.ItemId = i.ItemId
                where
                    ri.RecipeId = @RecipeId
                union all
                select
                    cm.CraftMaterialId,
                    cm.ItemId,
                    cm.RecipeId,
                    cm.Amount,
                    i.Name,
                    i.IconName,
                    cm.ItemId ChildItem,
                    cteMaterials.MatLevel + 1
                from
                    RecipeItem as ri
                    inner join CraftMaterial as cm
                        on cm.RecipeId = ri.RecipeId
                    inner join Item as i
                        on cm.ItemId = i.ItemId
                    inner join cteMaterials
                        on cteMaterials.ChildItem = ri.ItemId
                )
    select
        cteMaterials.CCraftMatId,
        cteMaterials.ItId,
        cteMaterials.RecId,
        cteMaterials.Amt,
        cteMaterials.Name,
        cteMaterials.Icon,
        cteMaterials.MatLevel
    from
        cteMaterials