The following is a recursive CTE that recursively retrieves the parent (3 layers) of [businessunit]:
WITH cte
AS (SELECT NULL AS [child],
e0.logicalname AS [parent],
e0.logicalname AS [path],
0 AS [depth]
FROM metadata.entity AS e0
WHERE e0.logicalname IN ('businessunit')
UNION ALL
SELECT rs.referencingentity AS [child], --(※1)
--cte.parent AS [child], (※2)
rs.referencedentity AS [parent],
cte.path + '/' + rs.referencedentity AS [path],
cte.depth + 1 AS [depth]
FROM cte AS cte
INNER JOIN
metadata.relationship_n_1 AS rs
ON cte.parent = rs.referencingentity
WHERE cte.depth < 3)
SELECT *
FROM cte
ORDER BY depth;
All [child] columns have the same value. This is clearly wrong.
actual
expected
When I used (2) instead of (1), the result changed. This is also strange.
The following is a recursive CTE that recursively retrieves the parent (3 layers) of [businessunit]: