asked on
How to show all descendants in parent child table
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,
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
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,
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
Microsoft SQL Server 2005
Last Comment
vikings24
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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?
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?
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_nam
from @person p --where parent_id is null
left join @person parent on p.parent_id=parent.person_
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
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_nam
from @person p --where parent_id is null
left join @person parent on p.parent_id=parent.person_
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
ASKER
thanks cyberkiwi, beautifully done
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,
from @person p
inner join cte on cte.person_id = p.parent_id
)
select distinct *
from cte