SQL query for nested records

Question

Given records within a table have parents within the same table and those parents have their own parents, what query will get me the records for all parents of a requested record provided that different records can have different number of parents.

Example: I have id of record 4 and want to get all the parent records (1,2,3). Of course I can get record 4, see who is the parent and go get record 3, see who's the parent and go get record 2, until the record I get has no parents, but is there a single query that can get me all the records?

Example Table:
ID | Type | Parent ID
----------------------------
1  |Master|
2  |Sub 1 | 1
3  |Sub 2 | 2
4  |Sub 3 | 3
5  |Master| 
6  |Sub 1 | 5

I can't get that query to work for me, I'm getting NULL. Can some one re-write with this example? — pmm, Apr 28 '12 at 20:38

Thomas · Accepted Answer · 2012-04-28T23:09:35.087

It is possible to represent the hierarchy if you assumed a fixed, maximum level of depth. For example, in this sample, I assume no more than four levels of depth. Without common-table expressions for recursive evaluation, there is no single query means beyond this type of solution or using a variable.

Select T1.Id, T1.Type
    , Concat( Coalesce( Concat(Cast(T4.Id As char(10)),','),'')
        , Coalesce( Concat(Cast(T3.Id As char(10)),','),'')
        , Coalesce( Concat(Cast(T2.Id As char(10)),','),''))
        As Hierarchy
From ExampleTable As T1
    Left Join ExampleTable As T2
        On T2.Id = T1.ParentId
    Left Join ExampleTable As T3
        On T3.Id = T2.ParentId
    Left Join ExampleTable As T4
        On T4.Id = T3.ParentId

SQL query for nested records

1 Answers1