Solved

How to show all descendants in parent child table

Posted on 2010-09-05
5
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 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