Scrolling without a cursor in SQL Server 2005

Question

I have an OrganizationStructure table 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 get a query that, if passing the let say application, OrganisatinID = 1means that it will be cyclically (looking at the parent / child) to the end of this table and grap all possible Returned OrganisatioIDs = (1, 2, 3, 4, 5).

Other if transferred OrganisationID = 2and then returnedOrganisationID = (2, 3)

Other if skipped OrganisationID = 3and then returnedOrganisationID = 3

Any ideas to do this without a cursor?

thanks

Answer 1

You can use SQL Server 2005 CTE to force the SQL engine to do this recursively.

http://blogs.msdn.com/anthonybloesch/archive/2006/02/15/Hierarchies-in-SQL-Server-2005.aspx

Celko SQL, n- .

, , , , @@ROWCOUNT (.. ). , , , , , .

Answer 2

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 3

SqlServer 2005 . . " " (CTE) SQL Server 2005 4guysfromrolla.

Answer 4

?

, // , , / .

, SQL 2005, , , , HierarchyID ( ) Sql 2008 .

Answer 5

3 , , .

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 that the question was answered quite well, however, if you are interested in alternative methods of structuring your data for a better effect, google for "evolution of ways to work with hierarchical data", I’m not yet allowed to post links :)