Find Multiple Finished Product code via 1 Raw Material Code
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);