Family tree Query Needed - 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)

Family tree Query Needed

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-10-13 : 03:27:12

Dear Sir/Madam,

I have a table called memberships where consist 3 field membershipid,sponsorid,uplineid

How am i going to write a function to loop all the member tree belong to that particular sponsor with tree level??

Please advise.

Thank you.

Regards,
Michelle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-13 : 03:58:38

You can use a recursive CTE.

N 56°04'39.26"
E 12°55'05.63"

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-10-13 : 04:40:13

Thank you Peso.

I'm get the solution already.

Here are the example:-

declare @temp table
(
parent varchar(20),
child varchar(20),
lev int
)

declare @lvl int,@membershipid int
select @lvl = 0,@membershipid=130

insert @temp (parent, child, lev)
select null, p.membershipid, @lvl
from memberships p
where p.membershipid =@membershipid

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

insert @temp (parent, child, lev)
select h.sponsorid, h.membershipid, @lvl
from @temp t
join memberships h on h.sponsorid= t.child and t.lev = @lvl-1
left join @temp x on x.parent = h.sponsorid and x.child = h.membershipid
where x.parent is null

end
select Sponsor=mm.MemberCode,B.MemberCode,lev
from
(
select parent,m.membercode,lev from @temp t
inner join memberships m on m.MembershipID=t.child
)B
inner join memberships mm on mm.MembershipID=B.parent

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-10-23 : 00:27:30

Sorry. Have 1 more question. Above i'm hardcoded the control to be only display 1 member family tree

declare @lvl int,@membershipid int
select @lvl = 0,@membershipid=130

How can i loop the membershipid from tablename: memberships; mean the family tree is more than a person.

Please advise

Thank you.

Regards,
Michelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources