Microsoft SQL - Bill of material, how to query only lowest parts in hierarchy-CodePudding

I have classic bill of material table:

PartId	SubPartId	Quantity
1	2	2
1	3	4
1	5	8
2	8	13

When given PartId, I want only those SubPartId that are not Parts (SubPartId is not present in PartId column), they are materials, so they are lowest in hierarchy. If given PartId = 1, I want query to return 3, 5, 8, because those parts don't have any subparts, not sure how to do it.

Tryed simple recursion:

with BOM as (
    SELECT  parts.PartId,
            parts.SubPartId
    FROM Parts parts
    WHERE parts.PartId = 1
    UNION ALL
    SELECT  components.PartId,
            components.SubPartId
    FROM Parts components
    JOIN BOM B
        on B.SubPartId = components.SubPartId )

From this query I get 2, 3, 5, 8, but I don't want 2 because it is not material.

CodePudding user response：

welcome to Stack Overflow.

Let's have a quick chat about providing demo data.

For a question like this the data and schema you're using is really helpful for us to be able to answer your question. Consider:

DECLARE @parts TABLE (PartID INT, SubPartID INT, Quantity INT)
INSERT INTO @parts (PartID, SubPartID, Quantity) VALUES
(1, 2, 2), (1, 3, 4),
(1, 5, 8), (2, 8, 13)

This produces a table variable describing your table and it's columns, and some sample data to work with.

On to your question!

From what you describe it seems like you want to know what the subparts of a given part are, which are not parts in their own right. Using the example above we could write this:

SELECT p.SubPartID
  FROM @parts p
    LEFT OUTER JOIN @parts p2
      ON p.SubPartID = p2.PartID
 WHERE p2.PartID IS NULL

Which produces the result set:

SubPartID
---------
3
5
8

What are we doing? We join back to the same table on the SubPartID column with a LEFT OUTER JOIN. Since we don't want the rows where there's a match we can then use the WHERE clause to filter out the non-nulls.

CodePudding user response：

I find using not exists is a natural fit here. Using the sample data provided by another answer here:

select *
from @parts as child
where not exists (
    select PartID
    from @parts as parent
    where parent.PartID = child.SubPartID
);

The query, in plain language, says "find rows where they are not the parent of another row". If you want to further reduce the set, you could apply a group by or distinct to the list of SubPartIDs.