Just thought I’d shared this. I wrote this SQL query to recursively retrieve all the part SRIDs when given a parent part SRID. This is the first time I’ve written a recursive SQL select and it turned out nice.
DECLARE @ParentSRID AS CHAR(36) = '123YOUR-PARENT-SRID-GOES-HERE456';
WITH cte_child_parts
AS (
-- BASE CASE
SELECT
p_sub.PartNum
, p_sub.SysRowID
FROM
dbo.Part AS p WITH (NOLOCK)
INNER JOIN dbo.PartMtl AS pm WITH (NOLOCK)
ON (pm.Company = p.Company AND
pm.PartNum = p.PartNum)
INNER JOIN dbo.Part AS p_sub WITH (NOLOCK)
ON (p_sub.Company = pm.Company AND
p_sub.PartNum = pm.MtlPartNum)
WHERE
p.SysRowID = @ParentSRID
UNION ALL
-- RECURSIVE CASE
SELECT
p_sub.PartNum
, p_sub.SysRowID
FROM
dbo.Part AS p WITH (NOLOCK)
INNER JOIN dbo.PartMtl AS pm WITH (NOLOCK)
ON (pm.Company = p.Company AND
pm.PartNum = p.PartNum)
INNER JOIN dbo.Part AS p_sub WITH (NOLOCK)
ON (p_sub.Company = pm.Company AND
p_sub.PartNum = pm.MtlPartNum)
INNER JOIN cte_child_parts AS cte
ON (cte.SysRowID = p.SysRowID)
WHERE
p.SysRowID = cte.SysRowID
)
SELECT *
FROM cte_child_parts