Find Multiple Finished Product code via 1 Raw Material Code On the SQL server in work we have multiple part codes which start with either "M" or "P" codes, once these M or P codes are used throughout the manufacturing process these are then assigned an "A" code of which there can be multiple "A" codes which are finally sold as "R" codes.

Therefore I'm looking to enter a specific M-code for example such as M1655 which links directly to the R-code without having to manually search by each A-code.

For example, the initial search of M1655 results in A02748.  I then need to search for A02748 which then leads on to A02749 then on to R3700, R3856 and R3857

I'm just not sure how I'd go about sorting that, there's a small selection of data below

Resource    PM Name   PM Description
M1655       A02748    HONEY RST SALMON FROZEN
A02748      A02749    HONEY RST SALMON DEFROSTED
A02749      R3770     LOCHMUIR SALMON PASTA
A02749      R3856     CAFE HONEY RST SALMON NICOISE
A02749      R3857     BFY HOT SALMON &amp; SUPERGREEN


Current SQL query details to get this info is:

SELECT pdResc.CalcValResource AS [Consumed Resource], pdPM.PM AS [PM Name], pdBalConsRescDt.Shrinkage,
        pdPM.Description AS [PM Description], fdBasResc.CatCodesCode2 AS [Resource BUSINESS], pdPM.PMSite AS [Site Name],
        fdBasResc_1.CatCodesCode2 AS [PM BUSINESS]
    FROM ERP_STD_CALE.dbo.fdBasResc AS fdBasResc,
        ERP_STD_CALE.dbo.fdBasResc AS fdBasResc_1,
        ERP_STD_CALE.dbo.pdBalConsRescDt AS pdBalConsRescDt,
        ERP_STD_CALE.dbo.pdPM AS pdPM,
        ERP_STD_CALE.dbo.pdResc AS pdResc,
        ERP_STD_CALE.dbo.pdStage AS pdStage
    WHERE pdPM.ObjectID = pdStage.ParentObjectID
        AND pdResc.ParentObjectID = pdStage.ObjectID
        AND pdResc.RescDtlObjectID = pdBalConsRescDt.ObjectID
        AND pdResc.CalcValRescOID = fdBasResc.ObjectID
        AND pdPM.PPRObjectID = fdBasResc_1.ObjectID
        AND (
              ( pdStage.ParentClassID = 10353 )
              AND ( pdStage.CollectionID = 1 )
              AND ( pdResc.ParentClassID = 10354 )
              AND ( pdResc.CollectionID = 1 )
              AND ( pdPM.PMInstType = 0 )
              AND ( pdPM.PMSite = '1000' )
              AND ( pdPM.AvailforSched = 1 )
            )
    ORDER BY fdBasResc.CatCodesCode2, pdResc.CalcValResource;
Does something like this help? It's a recursive CTE.

DECLARE @crap TABLE (Resource VARCHAR(6), PMName VARCHAR(6), PMDawn VARCHAR(50))
INSERT @crap ( Resource, PMName, PMDawn )
SELECT *
FROM (
VALUES('M1655' ,      'A02748',    'HONEY RST SALMON FROZEN'),
      ('A02748',      'A02749',    'HONEY RST SALMON DEFROSTED'),
      ('A02749',      'R3770' ,    'LOCHMUIR SALMON PASTA'),
      ('A02749',      'R3856' ,    'CAFE HONEY RST SALMON NICOISE'),
      ('A02749',      'R3857' ,    'BFY HOT SALMON &amp; SUPERGREEN')
) x (Resource, PMName, PMDawn);


WITH r_cte AS (
        SELECT  c.Resource, c.PMName, 0 AS PMLevel, CONVERT(VARCHAR(900), c.Resource) AS FullPath
        FROM @crap AS c
        WHERE c.Resource = 'M1655'

        UNION ALL

        SELECT c2.Resource, c2.PMName, PMLevel + 1, CONVERT(VARCHAR(900), CONCAT(r.FullPath, '/', c2.Resource))
        FROM r_cte r
        JOIN @crap AS c2
        ON r.PMName = c2.Resource
)
SELECT DISTINCT c.PMName, c.PMDawn
FROM r_cte r
JOIN @crap AS c
ON r.Resource = c.Resource
WHERE c.PMName LIKE 'R%'
OPTION(MAXRECURSION 0);