SQLTeam.com | Weblogs | Forums

Bill Of Materials Table get top-level part number


Hello Everybody,

This is my first post, so please excuse me if I'm asking something that has already been asked.

I have a table that's used to store parent-child relationships for a multi-level bill of materials. We'll call the table 'BOM'. In this table there are a handful of columns, but the ones critical to this question are "ParentID" and "ChildID".

I have a list of part numbers that I need to get all the "Top-Level" (no parents) part numbers that the initial list of parts numbers are used in.

I found this topic on this site, which might be what I'm looking for, but I'm having a hard time applying it to my situation.

Any help would be greatly appreciated,


-- I do not know all your columns names so I will make up some where needed.
-- It sounds like you want a list of rows from BOM where ParentID is missing.
Try the following:

select part_number from BOM where ParentId IS NULL -- Show Top-Level part numbers
or to reduce the list using:
select * from BOM where ParentId IS NULL and part_number in ('Part 1', 'Part 2', 'Part 3')

-- If this is not correct please provide more details. The best method is an example of your input rows and desired output rows.


I'd love to provide some sample data, but I don't know the best format... I haven't been a member on this site long enough to include pictures, I don't see how to attach an excel file, and I'm not seeing an option to add a table to this post... How would you like me to display the sample data?