SQL - Multi Level BOM

I need help with generating a query to pull from a table called "Mat_Req" to pull a multi-level BOM.  When I type in the job# in my query, it would pull both purchased and fabricated parts.  I am only interested in the sub component purchased materials to make these fabricated parts.  So my main query, I had filtered down to only pulling fabricated items.

Here's the query I am using:

SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
FROM MAT_REQ
WHERE TYPE = 'FAB' AND JOB_ID = '12454'

The above query would pull the highlighted items in Green as per the image below.  The item in green are all linked to job 12454, but the items not highlighted are different jobs to make that fabricated part.  I need help to pull those items.

Raw Table Data.  Noticed that line 7 and 8, Parent ID aaa10 require 2 materials to make that one.  First one was a purchased item and second is a fabricated item.  That fabricated item has a purchased raw material to make it so I also need to drill down.  Some jobs can have as deep as 5 or so multi-levels.
 Multi-RawData.jpg
This is my final output.  Only the purchased materials from the sub components when I put in the main Job#:
Final OutputMulti-RawData.jpg
Multi-FinalData.jpg
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Sounds like you're going to need a recursive cte (common table expression).  It would be helpful if you would provide table creation and population scripts.  This is not one that can be easily done with air code and is going to require some tinkering to get right.
0
holemaniaAuthor Commented:
Here's the data in my table or fields that I need.  With my basic query, I was able to get the 4 main components that are fabricated parts.  I then need to find the sub assemblies which is the result of job 8822, 88271, and 9999 to get the sub components that are purchased materials to make those 4 main components.

WITH BOM(JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
AS
(
SELECT '12454', 'A', 'Fab', 1, 'a1', 'Purc', 1
UNION
SELECT '12454', 'B', 'Fab', 1, 'c3', 'Fab', 1
UNION
SELECT '8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2
UNION
SELECT '12454', 'D', 'Fab', 2, 'd2', 'Purc', 4
UNION
SELECT '12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2
UNION
SELECT '9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2
UNION
SELECT '9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2
UNION
SELECT '88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2
)

SELECT * FROM BOM
0
holemaniaAuthor Commented:
Just to clarify the above my query would only pull the 4 lines.

SELECT JOB_ID, PARENT_ID, TYPE, QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY
 FROM MAT_REQ
 WHERE TYPE = 'FAB' AND JOB_ID = '12454'

Open in new window


SELECT '12454', 'A', 'Fab', 1, 'a1', 'Purc', 1
 UNION
 SELECT '12454', 'B', 'Fab', 1, 'c3', 'Fab', 1
 UNION
 SELECT '12454', 'D', 'Fab', 2, 'd2', 'Purc', 4
 UNION
 SELECT '12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2

I then need to look at the MAT_REQ for any jobs that are considered sub assemblies/components to make the above 4 main parts.  This is where I am stuck at and not sure how to proceed since I do not know how many levels deep it can go or what recursive I need to do to query the rest of the fab parts until it can get to the purchased parts and then list that.

So the other 4 lines if I was to manually search it, it's all in that same table just that it's a separate job to make that fabricated part use in the main job.  These separate jobs can have fabricated parts as well and so on and so on.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brian CroweDatabase AdministratorCommented:
Below is a recursive query that should get you moving in the right direction.  I am still unclear on the logic for your output.

DECLARE @JobID		VARCHAR(20);

DECLARE @BOM TABLE
(
	JOB_ID			VARCHAR(20),
	PARENT_ID		VARCHAR(20),
	[TYPE]			VARCHAR(20),
	QTY				INT,
	COMPONENT_ID	VARCHAR(20),
	COMPONENT_TYPE	VARCHAR(20),
	COMPONENT_QTY	INT
);
	
INSERT INTO @BOM (JOB_ID, PARENT_ID, [TYPE], QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
VALUES ('12454', 'A', 'Fab', 1, 'a1', 'Purc', 1),
	('12454', 'B', 'Fab', 1, 'c3', 'Fab', 1),
	('8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2),
	('12454', 'D', 'Fab', 2, 'd2', 'Purc', 4),
	('12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2),
	('9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2),
	('9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2),
	('88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2);

SELECT @JobID = '12454';

WITH cteBOM AS
(
	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, BOM.COMPONENT_ID AS ROOTCOMPONENT_ID,
		0 AS [LEVEL]
	FROM @BOM AS BOM
	LEFT OUTER JOIN @BOM AS BOMParent
		ON BOM.PARENT_ID = BOMParent.COMPONENT_ID
	WHERE BOM.[TYPE] = 'FAB'
		AND BOM.JOB_ID = @JobID
		AND BOMParent.COMPONENT_ID IS NULL

	UNION ALL

	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, cteBOM.ROOTCOMPONENT_ID,
		cteBOM.[LEVEL] + 1 AS [LEVEL]
	FROM cteBOM
	INNER JOIN @BOM AS BOM
		ON cteBOM.COMPONENT_ID = BOM.PARENT_ID
	WHERE BOM.COMPONENT_TYPE = 'Purc'
)
SELECT * FROM cteBOM

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Thanks.  I will take a look.  Looks like it only goes 1 level deep.  If it's more than 1 level, do I need to do another "Union all" and add the 2nd query and change the JOIN?
0
Brian CroweDatabase AdministratorCommented:
No, the common-table expression recursion has a maximum depth of 32767 which I'm sure is more than enough for your case.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
holemania, do you still need help with this question?
0
holemaniaAuthor Commented:
Sorry had been out and just got back in the office today.  I ran the example Brian provided.  Seems to be working somewhat.  However, I would expect the result to also include in the last row with job ID 88271 as a level 2.  However, this is not showing up.

My final result or output has ddd10.  Basically what you had, and if I was to filter just the component_type to purc, it would give me most of my result, but then it doesn't seem to be going any more level deep?

Here's my result after tweaking the select statement to get just the purc items.  It doesn't seem to go deeper.

DECLARE @JobID		VARCHAR(20);

DECLARE @BOM TABLE
(
	JOB_ID			VARCHAR(20),
	PARENT_ID		VARCHAR(20),
	[TYPE]			VARCHAR(20),
	QTY				INT,
	COMPONENT_ID	VARCHAR(20),
	COMPONENT_TYPE	VARCHAR(20),
	COMPONENT_QTY	INT
);
	
INSERT INTO @BOM (JOB_ID, PARENT_ID, [TYPE], QTY, COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY)
VALUES ('12454', 'A', 'Fab', 1, 'a1', 'Purc', 1),
	('12454', 'B', 'Fab', 1, 'c3', 'Fab', 1),
	('8822', 'c3', 'Fab', 1, 'a5', 'Purc', 2),
	('12454', 'D', 'Fab', 2, 'd2', 'Purc', 4),
	('12454', 'E', 'Fab', 1, 'aaa10', 'Fab', 2),
	('9999', 'aaa10', 'Fab', 2, 'bbb10', 'Purc', 2),
	('9999', 'aaa10', 'Fab', 2, 'ccc10', 'Fab', 2),
	('88271', 'ccc10', 'Fab', 2, 'ddd10', 'purc', 2)
	;

SELECT @JobID = '12454';

WITH cteBOM AS
(
	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, BOM.COMPONENT_ID AS ROOTCOMPONENT_ID,
		0 AS [LEVEL]
	FROM @BOM AS BOM
	LEFT OUTER JOIN @BOM AS BOMParent
		ON BOM.PARENT_ID = BOMParent.COMPONENT_ID
	WHERE BOM.[TYPE] = 'FAB'
		AND BOM.JOB_ID = @JobID
		AND BOMParent.COMPONENT_ID IS NULL

	UNION ALL

	SELECT BOM.JOB_ID, BOM.[TYPE], BOM.QTY, BOM.COMPONENT_ID, BOM.PARENT_ID,
		BOM.COMPONENT_TYPE, BOM.COMPONENT_QTY, cteBOM.ROOTCOMPONENT_ID,
		cteBOM.[LEVEL] + 1 AS [LEVEL]
	FROM cteBOM
	INNER JOIN @BOM AS BOM
		ON cteBOM.COMPONENT_ID = BOM.PARENT_ID
	WHERE BOM.COMPONENT_TYPE = 'Purc'
)
SELECT	COMPONENT_ID, COMPONENT_TYPE, COMPONENT_QTY, QTY*COMPONENT_QTY EXT_QTY
FROM	cteBOM
WHERE	COMPONENT_TYPE = 'PURC'
ORDER BY CASE COMPONENT_ID WHEN 'a1' THEN 0
							 WHEN 'a5' THEN 1
							 WHEN 'd2' THEN 2
							 WHEN 'bbb10' THEN 3
							 END

Open in new window

0
holemaniaAuthor Commented:
Here's the result with the query.

Query Result
0
holemaniaAuthor Commented:
Modified the "WHERE" clause a bit and got it to work now.  I just filtered "WHERE BOM.COMPONENT_TYPE = 'Purc'" from the 2nd query within the CTE code block and that fixed it.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.