fetch nodes to maximum depth

• gsriar

  Newbie

  Points: 9

  More actions

  June 27, 2007 at 3:00 am

  #124512

  hi

  i am have a table of structure

  table users

  (

  userid varchar(20),

  parent_userid varchar(20)

  )

  which makes a tree like structure

  i want to fetch all the userid's which are rooted

  under parent_userid to the maximum depth

  using stored procedure or function

  can any one tell me how can i solve this problem using recursion

  greatly thankful to all who may help

  Reply With Quote

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 27, 2007 at 5:45 am

  #715486

  Hello

  If you know the maximum depth, or there's likely to be a sensible maximum depth, then you don't necessarily need to use recursion. If this is indeed the case then you could use something like the following code. I'm taking some liberties here because you haven't stated how you wish your output to appear.

  CREATE table #users (userid varchar(20), parent_userid varchar(20))

  INSERT INTO #users (userid, parent_userid)
  SELECT '00000001', NULL UNION ALL
  SELECT '00000002', NULL UNION ALL
  SELECT '00000003', NULL UNION ALL
  SELECT '00000004', NULL UNION ALL
  SELECT '10000001', '00000001' UNION ALL
  SELECT '10000002', '00000001' UNION ALL
  SELECT '10000003', '00000001' UNION ALL
  SELECT '10000004', '00000001' UNION ALL
  SELECT '10000011', '00000002' UNION ALL
  SELECT '10000012', '00000002' UNION ALL
  SELECT '10000013', '00000002' UNION ALL
  SELECT '10000014', '00000002' UNION ALL
  SELECT '10000111', '00000003' UNION ALL
  SELECT '10000112', '00000003' UNION ALL
  SELECT '10000113', '00000003' UNION ALL
  SELECT '10000114', '00000003' UNION ALL
  SELECT '20001111', '10000111' UNION ALL
  SELECT '20001112', '10000112' UNION ALL
  SELECT '20001113', '10000113' UNION ALL
  SELECT '20001114', '10000114' UNION ALL 
  SELECT '30000001', '20001114' UNION ALL
  SELECT '30000002', '20001114' UNION ALL
  SELECT '10000114', '00000002' 

  SELECT u0.userid AS userid00, u1.userid AS userid01, u2.userid as userid02, 
   u3.userid as userid03, u4.userid as userid04, u5.userid as userid05 
  FROM (SELECT userid FROM #users WHERE parent_userid IS NULL) u0 -- identify top-level nodes first
  LEFT JOIN (SELECT userid, parent_userid FROM #users) u1 ON u1.parent_userid = u0.userid 
  LEFT JOIN (SELECT userid, parent_userid FROM #users) u2 ON u2.parent_userid = u1.userid 
  LEFT JOIN (SELECT userid, parent_userid FROM #users) u3 ON u3.parent_userid = u2.userid
  LEFT JOIN (SELECT userid, parent_userid FROM #users) u4 ON u4.parent_userid = u3.userid
  LEFT JOIN (SELECT userid, parent_userid FROM #users) u5 ON u5.parent_userid = u4.userid

  DROP TABLE #users

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Mark Harr

  SSCrazy Eights

  Points: 9921

  More actions

  June 27, 2007 at 9:17 am

  #715568

  Here is a function I wrote that would handle the same type of structure to whatever level you have.  The WHILE loop continues as long as new properties (users) are added to the next level.  Once a level returns no rows (is empty) then it exits, and the final query will return all distinct propertyid's (userid's).  The column names are parentproperty and childproperty (instead of parent_userid and userid, respectively), but should work the same for you.  It returns all the properties (users) underneath a given propertyid (userid):

  CREATE FUNCTION [dbo].[udf_ExplodeProperty] ( @arg int)
    RETURNS @tbl TABLE (propertyid int)
  AS
  BEGIN  DECLARE @tmptbl table (propertyid int, lvl int)  DECLARE @m int  SET @m = 0  insert @tmptbl    values(@arg, @m)  WHILE @@rowcount > 0   BEGIN     SET @m = @m + 1    insert @tmptbl      select childproperty as propertyid, @m as lvl      from propertygroup       where parentproperty IN (          SELECT propertyid from @tmptbl          WHERE lvl=@m-1)        AND childproperty NOT IN (SELECT propertyID from @tmptbl)  END  INSERT @tbl     SELECT DISTINCT propertyid    FROM @tmptbl;  RETURN
  END

  Then you can call this function to get your child properties (userids):

  SELECT propertyid from dbo.udf_ExplodeProperty(1)

  where (1) is the propertyid (userid) for the parent id you want to check.

  Hope this helps

  
  
  Mark