So I have an issue that I’m trying to resolve here.
Consider a table that contains the following:
CustomerGUID CustomerID CustomerName DepartmentName ParentGUID
xxxxxxxxxx 1 Customer1 Corporate NULL
xxxxxuxxxx 2 Customer1 Department1 xxxxxxxxxx
xxxxxyxxxx 3 Customer1 SubDepartment1 xxxxxuxxxx

I need to return the Customer ID, and the ROOT customer ID for all of the departments (in this case, it’s xxxxxxxxxx). I tried a recursive CTE to do this, but for some reason I can’t get it to work correctly. Ideally, the output should be:
CustomerID CustomerName DepartmentName RootCustomerID
1 Customer1 Corporate 1
2 Customer1 Department1 1
3 Customer1 SubDepartment1 1

The CTE code I tried is below:

;WITH root_customerID_cte AS (
SELECT
CustomerGUID,
CustomerID,
CustomerName,
DepartmentName,
ParentGUID,
1 AS level
FROM dbo.Customer
WHERE ParentGUID IS NULL
AND
CustomerName LIKE ‘%Customer1%’
UNION ALL
SELECT
c.CustomerGUID,
c.CustomerID,
c.CustomerName,
c.DepartmentName,
c.ParentGUID,
level + 1
FROM dbo.Customer c
INNER JOIN root_customerID_cte r
ON c.ParentGUID = r.CustomerGUID
WHERE
c.CustomerName LIKE ‘%Customer1%’
)
SELECT *
FROM root_customerID_cte

Any suggestions?