CODEWITHSUNDEEP
sqlsql-server
joelforsyth
joelforsyth

Reputation: 1021

Find multiple top level parents in CTE

I'm writing a pretty standard CTE to recursively look up a tree and find the top parent. The top parent is determined if OrganizationID_Parent is null. I pass in a managerID and I want to know the toppest level organizations of the organizations they manage.

Here's my CTE

;WITH OrgCTE AS
(
    SELECT o.OrganizationID_Parent , o.OrganizationID, 1 AS Lvl FROM Organization o
    WHERE OrganizationID IN (SELECT OrganizationID FROM OrganizationManager WHERE AdjusterID_Mgr = @ManagerID)

    UNION ALL

    SELECT o.OrganizationID_Parent, cte.OrganizationID, Lvl + 1 AS Lvl 
    FROM dbo.Organization o
    INNER JOIN OrgCTE cte ON o.OrganizationID = cte.OrganizationID_Parent

)

SELECT * FROM OrgCTE

The issue I am having is that my results are strange. The tree is like this:

OrganizationID_Parent    OrganizationID
         NULL                  1
         NULL                  2
          2                   137
         137                  140

My subselect returns 1 and 140, and the whole query returns parents mismatched and incorrect data. I need the query to return 1 and 2 since they are the top parents.

Here are the results I'm getting:

OrganizationID_Parent    OrganizationID
         NULL                  1
         137                  140
          2                   140
         NULL                 140

Here are the results I want:

OrganizationID_Parent    OrganizationID
         NULL                  1
         137                  140
          2                   137
         NULL                  2

Upvotes: 2

Views: 228

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Just a minor update. In your recursive part of CTE you are selecting initial OrganizationID instead of value from parent row.

;WITH OrgCTE AS
(
    SELECT o.OrganizationID_Parent , o.OrganizationID, 1 AS Lvl FROM Organization o
    WHERE OrganizationID IN (SELECT OrganizationID FROM OrganizationManager WHERE AdjusterID_Mgr = @ManagerID)

    UNION ALL

    SELECT o.OrganizationID_Parent, /*changes here o instead of cte */o.OrganizationID, Lvl + 1 AS Lvl 
    FROM dbo.Organization o
    INNER JOIN OrgCTE cte ON o.OrganizationID = cte.OrganizationID_Parent

)

SELECT * FROM OrgCTE

Upvotes: 2

Related Questions