August 19, 2008 at 9:11 am
I have a self reference table
Id is the primary key and the FK is going to Parent Id.
I basically want to send into my proc the id and then find all the children. The Children can also have children. I can do this using a cursor but that is so out dated. Is there another good way through a udf or a CTE or something like that so it would be an inline statement.
August 19, 2008 at 11:46 am
CTE will be your best option. A sample CTE for starting at a specific ID type situation is below:
[font="Courier New"];WITH OrgHierarchy (OrgID, OrgName) AS (
SELECT poi.OrgID, poi.OrgName
FROM orgOwner.ORGOrganization poi
WHERE poi.OrgID = @OrgID
UNION ALL
SELECT oi.OrgID, oi.OrgName
FROM orgOwner.ORGOrganization oi
INNER JOIN OrgHierarchy oh ON oi.ParentOrgID = oh.OrgID)
SELECT * FROM OrgHierarchy[/font]
Once you have the CTE OrgHierarchy, you can use it anywhere in a query or subquery in the statement following it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply