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

Question

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...

See [this topic](http://technet.microsoft.com/en-US/library/ms186243(v=sql.100).aspx) — GriGrim, Aug 30 '13 at 07:21

Tim Schmelter · Accepted Answer · 2013-08-30T07:37:53.400

5

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

edited Aug 30 '13 at 07:37

answered Aug 30 '13 at 07:28

Tim Schmelter

450,073
74
686
939

Is there recursive query on Oracle? – chance Aug 30 '13 at 07:37
I'm not that familiar with oracle (anymore), but it seems to be supported since 11g release2: http://stackoverflow.com/questions/4659803/recursion-in-oracle – Tim Schmelter Aug 30 '13 at 07:39

score 4 · Answer 2 · answered Aug 30 '13 at 07:27

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

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

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

2 Answers2