The following bit of code retrieves the latest bill of materials for a part:
Expand|Select|Wrap|Line Numbers
- SELECT b.BOMPartID, b.ComponentPartID, b.Quantity, b.ScrapPercent
- FROM BillOfMaterials b
- JOIN Versions v ON v.VersionID = b.VersionID
- WHERE v.StatusID = 1
- AND b.PrimeComponent = 1
I have tried using multiple LEFT JOINs for the BillOfMaterials table (where the ComponentPartID in the first table is linked to the BOMPartID in the second copy of the table) and I almost get what I want.
Expand|Select|Wrap|Line Numbers
- SELECT b1.BOMPartID,
- CASE
- WHEN b7.ComponentPartID IS NOT NULL THEN b7.ComponentPartID
- WHEN b6.ComponentPartID IS NOT NULL THEN b6.ComponentPartID
- WHEN b5.ComponentPartID IS NOT NULL THEN b5.ComponentPartID
- WHEN b4.ComponentPartID IS NOT NULL THEN b4.ComponentPartID
- WHEN b3.ComponentPartID IS NOT NULL THEN b3.ComponentPartID
- WHEN b2.ComponentPartID IS NOT NULL THEN b2.ComponentPartID
- END
- FROM BillOfMaterials b1
- LEFT JOIN BillOfMaterials b2 ON b2.BOMPartID = b1.ComponentPartID
- LEFT JOIN BillOfMaterials b3 ON b3.BOMPartID = b2.ComponentPartID
- LEFT JOIN BillOfMaterials b4 ON b4.BOMPartID = b3.ComponentPartID
- LEFT JOIN BillOfMaterials b5 ON b5.BOMPartID = b4.ComponentPartID
- LEFT JOIN BillOfMaterials b6 ON b6.BOMPartID = b5.ComponentPartID
- LEFT JOIN BillOfMaterials b7 ON b7.BOMPartID = b6.ComponentPartID
The number of sub assemblies that exist may vary from 0 to ??. From running through the LEFT JOIN I can see that currently there are up to 6 levels but there is nothing to stop the bill of materials having any number.
I also can't get the 'WHERE' conditions working in this version but I have not tried many things as I don't think this is the way I should be doing it anyway.
Any ideas on how to achieve what I am after would be much appreciated!