[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

bom where used

I have two tables, one that consists of a parts list and the other that contains bom information with parent and child.  In need to take the list of parts and find all of the parent part numbers where the parts list exist. The parts from the parts list table could be in parent part numbers or could be in higher assemblIes.   How would I do this?

Parts List Table

BOM Table
----------      ------------
ParentID      ChildPartID
  • 4
  • 3
  • 2
  • +1
1 Solution
Daniel WilsonCommented:
This is a classic hierarchy problem.  In SQL 2005 & 2008 we got the CTE that can be recursive ... and can help with such things.


If that's not enough help, I'll try to write you some code!
FairfieldAuthor Commented:
If you could write some sample code, I would appreciate it.
Chris LuttrellSenior Database ArchitectCommented:
Can you show the complete table structure of the Parts List table, are there columns to indicate Head Assemblies or sub-assemblies and such?  The question when you want to do recurrsion is where are you starting, you either have to pick a part and work up to the parent(s) or start with the top Assembly and work down to all the subs and parts.  I am not sure what you are wanting yet.  To "find all of the parent part numbers where the parts list exist" I read that as finding all parts that are parents and have sub-parts and you can get that with something like this:
      Select *
      From [Parts List Table]
      Where PartId in (Select ParentId From [BOM Table])
That will return all Parts that are Parents but that may include Sub-Assemblies that are components of other Parts and not necessarily stand alone.
Does that make any sense?  I am just trying to get to the root of your question.
It may also help to provide a small sample of data that either I or Daniel could use to help provide example code.
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Daniel WilsonCommented:
Here are the tables with which I'm working.  Am I close?

I'm working on the CTE query ...

Create Table dbo.Parts
( PartID int)

Create Table dbo.BOM
( ParentID int,
	ChildPartID int)

Insert Into Parts(PartID) Values(1)	
Insert Into Parts(PartID) Values(2)
Insert Into Parts(PartID) Values(3)
Insert Into Parts(PartID) Values(4)
Insert Into Parts(PartID) Values(5)
Insert Into Parts(PartID) Values(6)
Insert Into Parts(PartID) Values(7)
Insert Into Parts(PartID) Values(8)
Insert Into Parts(PartID) Values(9)
Insert Into Parts(PartID) Values(10)
Insert Into Parts(PartID) Values(11)	
Insert Into Parts(PartID) Values(12)
Insert Into Parts(PartID) Values(13)
Insert Into Parts(PartID) Values(14)
Insert Into Parts(PartID) Values(15)
Insert Into Parts(PartID) Values(16)
Insert Into Parts(PartID) Values(17)
Insert Into Parts(PartID) Values(18)
Insert Into Parts(PartID) Values(19)


Insert Into BOM(ParentID, ChildPartID) Values (8,9)
Insert Into BOM(ParentID, ChildPartID) Values (8,7)
Insert Into BOM(ParentID, ChildPartID) Values (8,6)
Insert Into BOM(ParentID, ChildPartID) Values (8,5)
Insert Into BOM(ParentID, ChildPartID) Values (2,8)

Open in new window

Daniel WilsonCommented:
This is what I've come up with, but it's wrong.

CG, do you know these CTE's better?

with MyCTE (Parent, Child)
	Select P.PartID, B.ChildPartID from Parts P left Join BOM B on P.PartID = B.ParentID
		--Where B.ParentID is NULL
	Select P.PartID, B.ChildPartID from Parts P Inner Join BOM B on P.PartID = B.ParentID
	Inner Join MyCTE as C on B.ChildPartID = C.Child
Select * from MyCTE Where Child =9

Open in new window

Mark WillsTopic AdvisorCommented:
OK, please have a look at the following....

Borrowed the create of the *testing purposes only* tables (except made them temp tables) from DanielWilson (thanks mate) see below in code snippet.

First up if just checking to see which Parts are in the BOM table, then just do :

SELECT * FROM #parts WHERE partid in (select distinct parentid from #bom union select distinct childpartid from #bom)

Now the CTE query is a lot more fun, so will also play with that... It creates a couple of additional columns - indented_tree and tree_path just to visualise the relationships.

(--Base Query
SELECT 1 AS Level,
convert(varchar(8000),right('000000'+convert(varchar,ParentID),6)) as Tree_Path,
convert(varchar(8000),right('000000'+convert(varchar,ParentID),6)) as Indented_Tree,
Parentid as PartID,
convert(int, NULL) as Parentid
From #BOM B
where not exists (select NULL from #BOM C where B.parentid = C.childpartid)

UNION ALL -- recursive bit

SELECT Level + 1,
tree_path + right('000000'+convert(varchar,B.ChildPartID),6), -- zero fills a 6 digit code
replicate(' ',Level+1) + convert(varchar(8000),right('000000'+convert(varchar,B.ChildPartID),6)) as Indented_Tree,
JOIN #BOM B ON B.Parentid = T.PartID

) --Final Select
INNER JOIN #parts P on B.PartID = P.PartID
ORDER BY Tree_path
-- more like a tree - makes path_tree_name more realistically indented
-- check it out if you order by level and partid - doesn't show relationship as a tree.

FairfieldAuthor Commented:
so here is my actual tables with sample data, please help

tbl802_parts (table)


vBOM (table)    

AS895US#ABA             1                KW944AV#ABA      
AS895US#ABA             1                GW678AV          
AS895US#ABA             1                FH032AV#ABA      
AS895US#ABA             1                FH631AV          
AS895US#ABA             1                FJ659AV#ABA      
Mark WillsTopic AdvisorCommented:
Well, that is interesting because no PART_NUM exists in vBOM

as shown by the query :

SELECT * FROM tbl802_parts WHERE part_num in (select distinct parent_part_num from vbom union select distinct child_part_num from vbom)

Which is probably better structured as :

SELECT * FROM tbl802_parts WHERE exists (select NULL from vbom where part_num in (parent_part_num,child_part_num))

or even :

SELECT distinct tbl802_parts.part_num  
FROM tbl802_parts
LEFT JOIN vBom P on tbl802_parts.part_num = P.Parent_part_num
LEFT JOIN vBom C on tbl802_parts.part_num = C.child_part_num
WHERE p.parent_part_num is not null or c.child_part_num is not null

But as for the CTE, there doesnt seem to be the requirement if all we need to do is to check the existance of part_num in the vBom. Maybe you could clarify ?

Daniel WilsonCommented:
Thanks for jumping in on this, Mark.  The CTE was giving me fits!
Mark WillsTopic AdvisorCommented:
No worries Daniel, but me thinks it might not be jist a CTE problem, beginning to think it is "show me parts that are used (anywhere) in bom", or maybe the BOM has entries that no longer exists, either way, I think there is more to it...

And while I think about it, we can also find parts used in BOM via :

SELECT distinct tbl802_parts.part_num  
FROM tbl802_parts
INNER JOIN vBom P on tbl802_parts.part_num in (P.Parent_part_num,P.child_part_num)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now