Loop through without Cursor in SQL Server 2005

Question

Hi there

I have a table OrganisationStructure like this:

OrganisationID INT
ParentOrganisationID INT
OrganisationName VARCHAR(64)

1 | 0 | Company
2 | 1 | IT DIVISION 
3 | 2 | IT SYSTEM BUSINESS UNIT
4 | 1 | MARKETING DIVISION
5 | 4 | DONATION BUSINESS UNIT

I want to have a query that if the app passing let say OrganisatinID = 1 means that it will loop (looking at parent/child) through till end of this table and grap all possible Returned OrganisatioIDs = (1, 2, 3, 4, 5).

Other if passing OrganisationID = 2 then Returned OrganisationID = (2, 3)

Other if passing OrganisationID = 3 then Returned OrganisationID = 3

Any ideas to do this without cursor?

Thanks

Answer 1

In SqlServer 2005 with Common Table Expressions is possible to do recursive queries. For an example see 'Recursive Common Table Expressions' in Common Table Expressions (CTE) in SQL Server 2005 from 4guysfromrolla.

Answer 2

You can use SQL 2005 CTEs to make the SQL engine do it recursively.

An enumeration of basic approaches is at http://blogs.msdn.com/anthonybloesch/archive/2006/02/15/Hierarchies-in-SQL-Server-2005.aspx

Celko also has a trees in SQL book which covers all of this to the nth degree.

Or you can brute force it by selecting each level into a local table variable and then looping, inserting children with a select, until your @@ROWCOUNT is zero (i.e., you're not finding any more children). If you don't have a lot of data, this is easy to code, but you hinted that you're looking for performance by saying you dont want a cursor.

Answer 3

How many levels deep can your parent child structure go ?

You could do a self-join on the table to line up grand-parent / parent / child entities, but that's limited by the number of levels deep your parent/child relationships can go.

I know you've stated SQL 2005 but just so you're aware this kind of tree structure mapping is exactly what the new HierarchyID (Video Here) in Sql 2008 is for.

Answer 4

declare @rootID int;
select @rootID = 4;
with cte_anchor as (
    SELECT OrganisationID
     , ParentOrganisationID
     , OrganisationName
    FROM Organisation
    WHERE OrganisationID = @rootID)
, cte_recursive as (
    SELECT OrganisationID
     , ParentOrganisationID
     , OrganisationName
    FROM cte_anchor
    UNION ALL
    SELECT o.OrganisationID
     , o.ParentOrganisationID
     , o.OrganisationName
    FROM Organisation o JOIN cte_recursive r
     ON o.ParentOrganisationID = r.OrganisationID)
SELECT * FROM cte_recursive

Answer 5

Try this for 3 levels using plain vanilla simple brute force - you can add levels as required.

SELECT DISTINCT OrganizationID  
FROM  
(  
    SELECT
    ParentOrganizationID  
    FROM OrganizationStructure  
    WHERE ParentOrganizationID = @arg

    UNION ALL  

    SELECT  
    OrganizationID  
    FROM OrganizationStructure  
    WHERE ParentOrganizationID = @arg  

    UNION ALL

    SELECT os2.OrganizationID  
    FROM OrganizationStructure os  
    JOIN OrganizationStructure os2 ON os.OrganizationID = is2.ParentOrganizationID  
    WHERE os.ParentOrganizationID = @arg   
) data

Answer 6

I believe the question is answered well enough, however if you're interested in alternative methods of structuring your data for better effect, google for 'evolt ways to work with hierarchical data' I'm not allowed to post links yet :)