Monday, October 8, 2012

SQL SERVER bottom to top order records hierarchy like parent and childid in a recursive CTE


SQL QUERY FOR TREE HIREARCHY BOTTOM TO TOP
DECLARE @LookupID int
--Our test value
SET @LookupID = 1
WITH cteLevelOne (ParentID, CustID) AS
(
        SELECT   a.ParentID, a.CustID
        FROM     tblCustomer AS a
        WHERE    a.CustID = @LookupID
    UNION ALL
        SELECT   a.ParentID, a.CustID
        FROM     tblCustomer AS a
        INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID
        WHERE c.CustID <> a.CustomerID)
So if tblCustomer looks like this:
ParentID    CustID5            5
1            8
5            4
4            1

No comments: