Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to show all descendants in parent child table

Posted on 2010-09-05
5
Medium Priority
?
410 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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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