How can I find all ancestors of a person? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How can I find all ancestors of a person?

Author

Topic

ashash
Starting Member

3 Posts

Posted - 2010-04-01 : 07:45:53

Hi
I have a table with two columns, fathers and sons.

father | son
______________
A | B
B | C
C | D

I want to find all ancestors of a person for example:
D's ancestors are: C,B,A

How can I do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 07:56:13

what is the version of SQL Server are you using ?

KH
[spoiler]Time is always against us[/spoiler]

ashash
Starting Member

3 Posts

Posted - 2010-04-01 : 08:02:01

2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:32:23

you can use the below solution


DECLARE @Temp table
(
father varchar(10),
son varchar(10),
lev int,
fullpath varchar(1000)
)

declare @lev int
set @lev = 0

INSERT @Temp
SELECT father,son,@lev,son
FROM YourTable
WHERE son='D'

while @@rowcount > 0
begin
       set @lev = @lev + 1

       INSERT @Temp (father,son, lev, fullpath)
       select t1.father, t1.son, @lev, t.fullpath + '.' + t1.son
       from   @Temp t
       join   YourTable t1 on t1.son = t.father and t.lev = @lev-1
       left join @Temp x on x.father = t1.father and x.son = t1.son
       where x.father is null
end

SELECT father,son
FROM @Temp
WHERE fullpath LIKE 'D%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashash
Starting Member

3 Posts

Posted - 2010-04-01 : 14:49:07

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:51:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources