Self join problem with tree structure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Self join problem with tree structure

I have the following table<br />Memberid int<br />submemberid int<br /><br />example <br />MemberId SubMemberId<br /><br /> 1 2<br /> 2 3<br /> 1 4<br /> 3 5<br /> 2 6<br /> 7 8<br /><br />Each member may supervise more than one member under him (whom I call submember)<br />and then each sub member may also supervise more than one member under him <br />I need to be able to select a specific member for example whose id is 1 and consequently all his sub members should be selected whose also all submembers should be selected and so<br />on <br />I do not know how to do this here is my trial:<br /><br /><br /><br /><pre>ALTER Procedure dbo.MemberReports;1<br /><br />@MemberMaskId nvarchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /><br /><br />As<br /> SET NOCOUNT ON<br /> Begin<br /> Declare @Exists Int — Return Value<br /><br /> <br /><br /> SELECT <br /> MembersTree.memberid, <br /> MembersTree.submemberid,<br /> MembersTree1.memberid<br /> FROM <br /> MembersTree<br /> INNER JOIN<br /> MembersTree MembersTree1<br /> ON MembersTree.Submemberid <br /> = MembersTree1.memberid<br /><br /> WHERE<br /> MembersTree.MemberId = @memId<br /><br /> If @@rowcount = 0 — No Record returned<br /> Select @Exists = 0<br /> Else<br /> Select @Exists = 1<br /> Return @Exists<br /><br /> End</pre><br /><br />Rasha zaki <br />Web Developer<br />Cairo, Egypt
Provided the depth is no larger than 32 sub members you can use a recursive User Defined Function. create table members (memberid int, submemberid int NULL)
insert members select 1,2
insert members select 2,3
insert members select 3,4
insert members select 4,NULL
insert members select 5,3
insert members select 5,3
create function FindSubMembers(@memberid int)
returns @submembers table (memberid int, submemberid int)
as
begin
declare @submember int
select @submember = submemberid from members where memberid = @memberid if @memberid is not null
BEGIN insert into @submembers
select memberid, submemberid
from members where memberid = @memberid
UNION
select memberid, submemberid
from dbo.FindSubMembers(@submember)
END
return
end
declare @memberid int
set @memberid = 1 select memberid, submemberid
from dbo.FindSubMembers(@memberid)
Version above only allows for one submember per member currently, but you can fix that by change the @submember variable to a table type and inserting to it before using the results to call the function recursively. Dave Hilditch.
Hello,
Thanks Dave for your reply. I tried to find a solution that does not depend on the depth of the tree and I found 2 usefule links and I put my code from those 2 links and I am posting them here for any body else facing the same problem
The following paragraph was included in the solution of msdn:
quote:The following Transact-SQL procedure expands an encoded hierarchy to any arbitrary depth. Although Transact-SQL supports recursion, it is more efficient to use a temporary table as a stack to keep track of all of the items for which processing has begun but is not complete.

My Code is: –/*/ Tree Structure With the help of
–/*/http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp –Create table as an example
Create MembersTree (Memberid int, Sub Memberid int)
–Insert date in the table
insert members select 1,2
insert members select 2,3
insert members select 3,4
insert members select 4,NULL
insert members select 5,3
insert members select 5,3 ALTER PROCEDURE expand (@current char(20)) AS
SET NOCOUNT ON
DECLARE @lvl int, @line char(20)
, @RecordID int, @RecordNo int
, @i int CREATE TABLE #stack (item char(20), Pitem char(20), lvl int)
CREATE TABLE #RstTable (ItemId int IDENTITY (1, 1) NOT NULL, ChildItem char(20), ParentItem char(20) , lvl int) INSERT INTO #stack VALUES (@current,0, 1)
INSERT INTO #RstTable ( ChildItem , ParentItem, lvl, MonthP) Values (@current,0,1)
SELECT @lvl = 1
— /*/ when @lvl is greater than 0,
— the procedure follows these steps:
WHILE @lvl > 0 BEGIN — /*/ If there are any items in the stack at the current
— /*/ level (@lvl), the procedure chooses one and calls it
— /*/ @current.
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN SELECT @current = item — Let the Child (item) now playes the role of a parent (@cuurent)
FROM #stack
WHERE lvl = @lvl — /*/ The following is For formatting the output in
— /*/ as a tree in the query analyzer
SELECT @line = space(@lvl – 1) + @current
PRINT @line
— /*/ End of formatting — /*/ Deletes the item from the stack so it will not
— /*/ be processed again, and then adds all its
— /*/ child items to the stack at the next level
— /*/ (@lvl + 1). This is the only place where the
— /*/ hierarchy table (#stack) is used.
–/*/ IMPRTANT NOTE
–/*/ With Transact-SQL, you can find all child items
–/*/ and add them with a –> single statement, <–
–/*/ avoiding another nested loop. DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current INSERT #stack
SELECT submemberid, memberid , @lvl + 1
FROM MembersTree
WHERE memberid = @current — The child now becoms a parent to another member INSERT #RstTable
SELECT submemberid, memberid , @lvl + 1
FROM MembersTree
WHERE memberid = @current –/*/ If there are child items (IF @@ROWCOUNT > 0),
–/*/ descends one level to process them
–/*/ (@lvl = @lvl + 1); otherwise, continues
–/*/ processing at the current level. IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1 END
ELSE
–/*/ If there are no items on the stack awaiting
–/*/ processing at the current level, goes back one
–/*/ level to see if there are any awaiting processing
–/*/ at the previous level (@lvl = @lvl – 1). When
–/*/ there is no previous level, the expansion is
–/*/ complete. SELECT @lvl = @lvl – 1
END — WHILE End
select * from #RstTable I Hope this helps, Thanks Dave again Rasha zaki
Web Developer
Cairo, Egypt
]]>