CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Selecting all parents in the order of relation from hierarchical table SQL

I've a table like this with a parent child relation in the same table

AccountID|     ParentID     |   AccountName
----------------------------------------------
1       |   0       |   Root
2       |   1       |   Child1
3       |   1       |   Child2
4       |   2       |   Child3
5       |   4       |   Child1
6       |   5       |   Child1
7       |   6       |   Child1
8       |   6       |   Child1

So when I send the account ID 7 I have to get the tables in the order like child,father,grandfather.. that way.. So for 7, I need to get all parets like this

AccountID
---------
    7
    6
    5
    4
    2
    1

So the most important point is the order. It should be from the bottom level to its next higher then to the next...

Upvotes: 5

Views: 2073

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460078

You can use a recursive CTE:

declare @childAccID int
set @childAccID = 7  

;WITH Rec_CTE 
    AS(
        SELECT 1 AS Level, 
               tChild.*
        FROM dbo.TableName tChild
        WHERE tChild.AccountID = @childAccID

        UNION ALL

        SELECT Level + 1 AS Level, 
               parent.*
        FROM Rec_CTE tParent
        INNER JOIN  dbo.TableName parent 
          ON parent.AccountID = tParent.ParentID
    )
SELECT * FROM Rec_CTE
ORDER BY Level

DEMO

Upvotes: 5

JohnLBevan
JohnLBevan

Reputation: 24410

Try this:

create table DemoTable
(
    accountid bigint
    ,parentid bigint
    ,accountname nvarchar(128)
)
insert DemoTable(accountid,parentid,accountname)
select 1, null, 'Root'
union select 2, 1, 'Child1'
union select 3, 1, 'Child2'
union select 4, 1, 'Child3'
union select 5, 2, 'Child1.1'
union select 6, 2, 'Child1.2'
go
declare @findMe bigint = 6;
with myCTE as
(
    select accountid,parentid,accountname,1 hierarchyLevel
    from DemoTable
    where accountid = @findMe

    union all

    select b.accountid,b.parentid,b.accountname, a.hierarchyLevel + 1
    from myCTE a
    inner join DemoTable b
    on b.accountid = a.parentid
)
select * from myCTE
order by hierarchyLevel

Upvotes: 4

Related Questions