quote:

---

Originally posted by dewacorp.alliances

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 Organisation IDs = (2, 3)

Other if passing OrganisationID = 3 then Returned Organisation ID = 3

Any ideas to do this without cursor?

---

;With Org_CTE (OrgID,POrgID,OrgName) AS
(SELECT OrganisationID ,ParentOrganisationID, OrganisationName 
FROM  OrganisationStructure 
WHERE OrganisationID=@ID
UNION ALL
SELECT os.OrganisationID ,os.ParentOrganisationID, os.OrganisationName 
FROM Org_CTE c
JOIN OrganisationStructure os
ON os.ParentOrganisationID=c.OrgID
)

SELECT *
FROM Org_CTE