SQL BOM Tree Joining

Imagine that all of this is in a single table as pictured. What I want is to retrieve the class of SKU 123 and 456 starting with the raw RM123. This seems like a looping join that I just can't get my head around. Help please.

This sounds like you want a recursive CTE (some background here and here).

With this table and sample data:

CREATE TABLE dbo.ImagineThis
(
  SKU   varchar(32),
  BOM   varchar(32), 
  CLASS varchar(32)
);

INSERT dbo.ImagineThis(SKU,BOM,CLASS) VALUES
('RM123' , '',       'D'),
('WPR123', 'RM123' , 'C'),
('WPR456', 'RM123' , 'C'),
('123'   , 'WPR123', 'B'),
('456'   , 'WPR456', 'A');

One approach is:

;WITH cte AS
(
  SELECT lvl = 1, SKU, BOM, CLASS, Path = CONVERT(nvarchar(max), SKU)
  FROM dbo.ImagineThis
  WHERE BOM = ''
  
  UNION ALL
  
  SELECT lvl = lvl + 1, i.SKU, i.BOM, i.CLASS, Path = cte.Path + '\' + i.SKU
  FROM cte
  INNER JOIN dbo.ImagineThis AS i
  ON i.BOM = cte.SKU
)
SELECT * FROM cte OPTION (MAXRECURSION 32000);

Results:

Though I'm not sure how you came to the answer "A" since these two paths seem "equal" and both A and B are at the same "level." You can see this by limiting the output to the highest level:

;WITH cte AS
(
  ...
)
SELECT TOP (1) WITH TIES *
  FROM cte 
  ORDER BY lvl DESC
  OPTION (MAXRECURSION 32000);

Why do you consider "A" as the final product?

• Example db<>fiddle

As for why A

"A" (class) would be the final answer in terms of heirachary. A = 1, B = 2, etc,. I'd create either a temp table or CTE with this definition.

Ok:

DECLARE @ClassHierarchy table(Class varchar(32), Ordering tinyint);
INSERT @ClassHierarchy VALUES('A',1),('B',2),('C',3),('B',4);

;WITH cte AS
(
  SELECT lvl = 1, SKU, BOM, CLASS, Path = CONVERT(nvarchar(max), SKU)
  FROM dbo.ImagineThis
  WHERE BOM = ''
  
  UNION ALL
  
  SELECT lvl = lvl + 1, i.SKU, i.BOM, i.CLASS, Path = cte.Path + '\' + i.SKU
  FROM cte
  INNER JOIN dbo.ImagineThis AS i
  ON i.BOM = cte.SKU
)
SELECT TOP (1) WITH TIES cte.*
  FROM cte 
  INNER JOIN @ClassHierarchy AS h
  ON cte.CLASS = h.Class
  ORDER BY lvl DESC, h.Ordering
  OPTION (MAXRECURSION 32000);

Results:

• Updated db<>fiddle