Build of Material - where used query - CTE and Recursion 1

[dhookom]

I'm attempting to create a query (probably cte with recursion) that would take a bill of materials (BOM) and identify all parts used in a finished product. I have searched the web and found a lot of recursion examples for org structures etc. but they don't seem to meet my needs.

I have a table BOM with 4 significant columns parent_id, parent_type, child_id, and child_type. The "type" fields are:
FINI for finished goods
ASMB for an assembly that can contain 1 or more PARTs
PART for parts which are the smallest units

A finished good can be comprised of assemblies or parts
An assembly is comprised of assemblies or parts
An assembly or part can be included in more than one finished good
Assemblies can be nested up to 20 levels

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [BOM](
	[parent_id] [int] NULL,
	[parent_type] [varchar](4) NULL,
	[child_id] [int] NULL,
	[child_type] [varchar](4) NULL
) ON [PRIMARY]
GO
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (2, N'FINI', 5, N'ASMB')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (2, N'FINI', 6, N'ASMB')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (3, N'FINI', 7, N'ASMB')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (3, N'FINI', 8, N'ASMB')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (4, N'FINI', 9, N'ASMB')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (4, N'FINI', 10, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (5, N'ASMB', 11, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (5, N'ASMB', 12, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (6, N'ASMB', 13, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (6, N'ASMB', 14, N'ASMB')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (7, N'ASMB', 15, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (8, N'ASMB', 16, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (8, N'ASMB', 17, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (9, N'ASMB', 18, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (9, N'ASMB', 19, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (14, N'ASMB', 20, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (2, N'FINI', 21, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (2, N'FINI', 22, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (4, N'FINI', 23, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (4, N'FINI', 24, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (4, N'FINI', 25, N'PART')
INSERT [BOM] ([parent_id], [parent_type], [child_id], [child_type]) VALUES (3, N'FINI', 9, N'ASMB')

I am looking for a result that identifies which parts are included in which finished goods.

[Pre]
parent_id parent_type child_id child_type
2 FINI 11 PART
2 FINI 12 PART
2 FINI 13 PART
2 FINI 20 PART
2 FINI 21 PART
2 FINI 22 PART
3 FINI 15 PART
3 FINI 16 PART
3 FINI 17 PART
3 FINI 18 PART
3 FINI 19 PART
4 FINI 18 PART
4 FINI 19 PART
4 FINI 23 PART
4 FINI 24 PART
4 FINI 25 PART
[/Pre]

Any help or direction to a web resource would be much appreciated.

Duane
Hook'D on Access
MS Access MVP

 

[gmmastros]

I think you probably had a hard time with the recursion because most of the examples you find out there don't display the "grand parent" information. Anyway... based on your sample data and expected results, I came up with this:

With RecursiveData (
        parent_id, 
        parent_type, 
        child_id, 
        child_type, 
        level, 
        ParentId, 
        ParentType)
As
(
  Select  parent_id, 
          parent_type, 
          child_id, 
          child_type, 
          1 As Level, 
          parent_id As ParentId, 
          parent_type As ParentType
  From    BOM
  Where   parent_type = 'FINI'

  Union All

  Select  b.parent_id, 
          b.parent_type, 
          b.child_id, 
          b.child_type, 
          RecursiveData.Level + 1, 
          RecursiveData.ParentId, 
          RecursiveData.ParentType
  From    BOM b
          Inner Join RecursiveData 
            On b.parent_id = RecursiveData.child_id
)
Select ParentId As parent_id,
       ParentType as parent_type,
       child_id,
       child_type 
From   RecursiveData 
Where  child_type = 'part' 
Order By parent_id

Note that I had to include a couple extra columns so that I could keep the parent's parent id and type (which you wanted in your final output).

BTW, I think you must 4,10 in your sample output.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhookom]

George,
Thanks, this seems to work exactly how I was hoping and yes, I did miss 4,10 (I hate when OPs provide bad expected results).
I owe you a cold one at the next Summit !

Duane
Hook'D on Access
MS Access MVP

 

[gmmastros]

Hopefully I'll be able to make it next year. BTW, I suggest you check the links in your signature.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhookom]

Thanks for the tip on the signature. Apparently MS has moved some pages around.

I also just noticed "Build of Materials" rather than "Bill of Materials"

Hopefully I'll get awarded again in October.

Duane
Hook'D on Access
MS Access MVP