Recursive CTE Not Getting Top Level Superseded Parts In T-SQL
Hello, I've been given a task to get the superseded partnumber of a certain part. The scenario is, the chain of the superseded is undetermined and could go as has high as four or more levels. See screenshot below of an example of product supersession. It illustrates that the part supersession of part M03971 is M32887 instead of its direct parent M04880. Trying out the concept of recursive CTE gets only the lowest level instead without it's parent part supersessions. WITH SupersededBy_Cte AS ( SELECT [StdDescID], [PartNumber], [SupersededBY], 0 AS [ level ] FROM [tblStdDesc] std1 WHERE std1.SupersededBY IS NULL UNION All SELECT std2.[StdDescID], std2.[PartNumber], std2.[SupersededBY], cte.[ level ] + 1 FROM SupersededBy_Cte cte INNER JOIN [tblStdDesc] std2 ON std2.SupersededBY = cte.StdDescID ) SELECT cte.[StdDescID], cte.[PartNumber], cte.[SupersededBY], cte.[ level ] FROM SupersededBy_Cte cte WH...
