Solved

How to show all descendants in parent child table

Posted on 2010-09-05
5
402 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks cyberkiwi, beautifully done
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

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…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

9 Experts available now in Live!

Get 1:1 Help Now