I need to build a query that returns the item master, plus the BOMs and sub BOMS. For example, the master BOM item is 'mealdeal'. There are three components in that - 'drink', 'chocolate bar' and 'sandwich'. 'Sandwich' is a BOM in itself, and may contain other BOMs depending on what is in the sandwich. I have written a query which is fine if I want to filter down to one item, as the lines it returns will just be the lines for that item, but I need to create a table to build other reports off. What I'm trying to achieve is a separate column which stamps / records the master item against all of the levels. This is what I have so far
WITH ExplodeBOM ([No.], [Production BOM No.], [Quantity], [Quantity per], [Unit of Measure Code], [BOM Level]) AS
(
SELECT [No.], [Production BOM No.], Quantity, [Quantity per], [Unit of Measure Code], CAST(1 AS VARCHAR(MAX)) AS [BOM Level]
FROM dbo.[Production BOM Line]
UNION ALL
SELECT [BOML1].[No.], [BOML1].[Production BOM No.], [BOML1].[Quantity], [BOML1].[Quantity per], [BOML1].[Unit of Measure Code], CAST([BOM Level] + 1 AS VARCHAR(MAX)) AS [BOM Level]
FROM dbo.[Production BOM Line] AS BOML1
INNER JOIN ExplodeBOM AS MasterBOM
ON BOML1.[Production BOM No.] = MasterBOM.[No.]
)
SELECT Item.[Production BOM No.], Description, Item.[No.],ExplodeBOM.[No.], ExplodeBOM.[Production BOM No.], [Quantity], [Quantity per], [Unit of Measure Code], [BOM Level]
FROM Item
inner join ExplodeBOM on Item.[Production BOM No.] = [ExplodeBOM].[Production BOM No.]
I thought I could achieve it by adding in the item table, but that doesn't work. Ideally I would like a results set that looks something like this
| MasterBOM | Component |
|---|---|
| MEALDEAL1 | COKE |
| MEALDEAL1 | CHOCOLATE |
| MEALDEAL1 | TURKEYSANDWICH |
| MEALDEAL1 | BREAD |
| MEALDEAL1 | MAYO |
| MEALDEAL1 | CHEESE |
| MEALDEAL2 | SPRITE |
| MEALDEAL2 | DORITOS |
| MEALDEAL2 | TUNASUB |
| MEALDEAL2 | SUBROLL |
| MEALDEAL2 | TUNA |
| MEALDEAL2 | CUCUMBER |
Where turkey sandwich and tuna sub are BOM components of mealdeal / mealdeal2, but they are BOMS themselves, which contain the ingredients to make them.
Do I need to change the recursive CTE somehow?