Solved

How to show all descendants in parent child table

Posted on 2010-09-05
5
403 Views
Last Modified: 2012-05-10
I have a parent child table in sql server 2005
I would like to select all descendants of each entry.
Using cte allows me to retrieve details from the immediate parent.
in this example, 4 reports to 3, 3 reports to 2, 2 reports to 1, 1 is the top

declare @person as table (person_id int, person_name varchar(20),parent_id int)
insert into @person      values (1,      'person 1'      ,null)
insert into @person      values (2,      'person 2'      ,1)
insert into @person      values (3,      'person 3'      ,2)
insert into @person      values (4,      'person 4'      ,3)

with cte as (
      select person_id, person_name,parent_id, convert (varchar (20), 'no parent') as parent_name
      from @person where parent_id is null
      union all
      select p.person_id, p.person_name,p.parent_id, cte.person_name as parent_name
      from @person p
      inner join cte  on cte.person_id = p.parent_id
)
select * from cte

returns
person_id      person_name parent_id      parent_name
1      person 1      NULL      no parent
2      person 2      1      person 1
3      person 3      2      person 2
4      person 4      3      person 3

I would like to return

person_id      person_name      parent_id      parent_name
1      person 1      NULL      no parent
2      person 2      1      person 1
3      person 3      2      person 2
4      person 4      3      person 3
--  additional redundant rows                  
3      person 3      1      person 1
4      person 4      2      person 2
4      person 4      1      person 1

is this possible?

thanks John



0
Comment
Question by:vikings24
  • 3
  • 2
5 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33608999
declare @person as table (person_id int, person_name varchar(20),parent_id int)
insert into @person      values (1,      'person 1'      ,null)
insert into @person      values (2,      'person 2'      ,1)
insert into @person      values (3,      'person 3'      ,2)
insert into @person      values (4,      'person 4'      ,3)

;with cte as (
      select person_id, person_name,parent_id, convert (varchar (20), 'no parent') as parent_name
      from @person --where parent_id is null
      union all
      select p.person_id, p.person_name,p.parent_id, cte.person_name as parent_name
      from @person p
      inner join cte  on cte.person_id = p.parent_id
)
select distinct *
from cte
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33609036
Correction

declare @person as table (person_id int, person_name varchar(20),parent_id int)
insert into @person      values (1,      'person 1'      ,null)
insert into @person      values (2,      'person 2'      ,1)
insert into @person      values (3,      'person 3'      ,2)
insert into @person      values (4,      'person 4'      ,3)

;with cte as (
      select person_id, person_name, parent_id,
            convert (varchar (20), 'no parent') as parent_name, 0 as level_removed
      from @person --where parent_id is null
      union all
      select p.person_id, p.person_name, cte.parent_id, cte.person_name as parent_name, level_removed+1
      from @person p
      inner join cte  on cte.person_id = p.parent_id
)
select person_id, person_name, parent_id, parent_name, max(level_removed)
from cte
where parent_id is not null or level_removed = 0
group by person_id, person_name, parent_id, parent_name
order by 5, person_id
0
 

Author Comment

by:vikings24
ID: 33609074
thanks cyberkiwi

very close - the person_id, person name and parent_id are exactly as I want.
The parent name is only correct for person1 (ie 'no parent') - I could easily run another step to correct this ( a join back to the original table), but is it possible to perform this in the one step?





0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33609351
Sure you can.
Sorry I didn't watch the parent_name column too closely.


declare @person as table (person_id int, person_name varchar(20),parent_id int)
insert into @person      values (1,      'person 1'      ,null)
insert into @person      values (2,      'person 2'      ,1)
insert into @person      values (3,      'person 3'      ,2)
insert into @person      values (4,      'person 4'      ,3)

;with cte as (
      select p.person_id, p.person_name, p.parent_id,
            coalesce(parent.person_name,'no parent') as parent_name, 0 as level_removed
      from @person p --where parent_id is null
        left join @person parent on p.parent_id=parent.person_id
      union all
      select p.person_id, p.person_name, cte.parent_id, cte.parent_name as parent_name, level_removed+1
      from @person p
      inner join cte  on cte.person_id = p.parent_id
)
select person_id, person_name, parent_id, parent_name, max(level_removed)
from cte
where parent_id is not null or level_removed = 0
group by person_id, person_name, parent_id, parent_name
order by 5, person_id
0
 

Author Comment

by:vikings24
ID: 33609390
thanks cyberkiwi, beautifully done
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now