Parent - Child Relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Parent - Child Relationship

Author

Topic

nkpriya
Starting Member

34 Posts

Posted - 2012-03-30 : 19:15:39

Need help in solving this query:
I have a table with child-parent relationship:
Id Child Parent
1 Bob Allan
2 Ash Allan
3 Allan William
4 Keith William
5 William John
6 Jai John

Now I want to achive something like this:
Id Child Parent
1 Bob John
2 Ash John
3 Allan John
4 Keith John
5 William John
6 Jai John

Basically all are john's reports but in different hierarchy levels. I know that we can do with CTE's but not sure how to do that.

Any ideas how to achieve this. Please let me know. Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-03-30 : 19:52:36

[CODE]declare @Tbl table (
id int identity(1,1),
Child varchar(20),
Parent varchar(20)
)

insert into @Tbl(Child, Parent)
values ('Bob', 'Allan'),
('Ash', 'Allan'),
('Allan', 'William'),
('Keith', 'William'),
('William', 'John'),
('Jai', 'John')

;with AllReports
as (
select Child, Parent
from @Tbl

union all

select tc.Child, tp.Parent
from AllReports tp
inner join @Tbl tc
on tp.Child = tc.Parent
)
select Child, Parent
from AllReports
where Parent = 'John'[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

nkpriya
Starting Member

34 Posts

Posted - 2012-04-02 : 11:23:46

No, the proposed solution is not working.

Btw, I just gave sample as 'John'. With the given solution, It is just giving me the existing child, immediante parent names but not giving child and the ultimate parent names.

Please suggest. Thanks

nkpriya
Starting Member

34 Posts

Posted - 2012-04-02 : 13:29:23

Just want to add more.

Looks like the proposed query is giving all results including the one in the existing table. How can I achive only the final relationship(means doing recursive until it reaches null). Please advise. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources