The following is a recursive CTE that recursively retrieves the parent (3 layers) of [businessunit]:
WITH cte
AS (SELECT NULL AS [referencingentity],
e0.logicalname AS [referencedentity],
e0.logicalname AS [path],
0 AS [recursion_count]
FROM metadata.entity AS e0
WHERE e0.logicalname IN ('businessunit')
UNION ALL
SELECT cte.referencedentity AS [referencingentity],
sub.referencedentity AS [referencedentity],
cte.path + '/' + sub.referencedentity AS [path],
cte.recursion_count + 1 AS [recursion_count]
FROM cte AS cte CROSS APPLY (SELECT DISTINCT rs.referencedentity
FROM metadata.relationship_n_1 AS rs
WHERE cte.referencedentity = rs.referencingentity) AS sub
WHERE cte.recursion_count < 3)
SELECT *
FROM cte
ORDER BY recursion_count;
Line 15 is wrong.
In fact, [organization] does not have [organization] as a parent.
The following is a recursive CTE that recursively retrieves the parent (3 layers) of [businessunit]:
Line 15 is wrong. In fact, [organization] does not have [organization] as a parent.![relationship_n_1_result](https://github.com/MarkMpn/Sql4Cds/assets/133593429/3c1e48e3-1bbd-4b1a-b0b6-7d18729c968c)