Solved

How to show all descendants in parent child table

Posted on 2010-09-05
5
408 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

636 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