bom where used

Posted on 2009-12-17
Last Modified: 2012-08-13
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
Question by:Fairfield
    LVL 32

    Expert Comment

    by:Daniel Wilson
    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!

    Author Comment

    If you could write some sample code, I would appreciate it.
    LVL 26

    Expert Comment

    by:Chris Luttrell
    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.
    LVL 32

    Expert Comment

    by:Daniel Wilson
    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

    LVL 32

    Expert Comment

    by:Daniel Wilson
    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

    LVL 51

    Accepted Solution

    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
    SELECT B.*
    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.


    Author Comment

    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      
    LVL 51

    Expert Comment

    by:Mark Wills
    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 ?

    LVL 32

    Expert Comment

    by:Daniel Wilson
    Thanks for jumping in on this, Mark.  The CTE was giving me fits!
    LVL 51

    Expert Comment

    by:Mark Wills
    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

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now