0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and researchers interested in open data

-- my top parent is declare when childID = parentID - so ive changed a bit.
-- but still it goes through endleess loop.
-- the expected retuls : 

/*childID - 1 , has top father - 20
childID - 5 , has top father - 20
childID - 9 , has top father - 20
childID - 20 , has top father - 20
childID - 2 , has top father - 4
childID - 3 , has top father - 8
childID - 7 , has top father - 8
childID - 8 , has top father - 8*/

--Also Enable the text - becuase  this site cant save error query.



declare   @myTable  table
	(
     childID int , 
     parentID int, 
     name nvarchar(30)
    );
 
INSERT INTO @myTable
select 1,5,'a'
union all
select 5,9,'b'
union all
select 9,20,'c'
union all
select 2,4,'d'
union all
select 3,7,'e'
union all
select 7,8,'f'
union all
 select 20,20,'r1'
union all
select 4,4,'r2'
union all
select 8,8,'r3'

 /*;WITH ChildParent AS
(
   SELECT childID
          ,parentID = CASE 
                           WHEN childID=parentID THEN -1
                           ELSE parentID
                      END
          ,[name]
          ,PLevel = 1	-- defines level, 1 = TOP, 2 = immediate child nodes etc.
          ,TopLevelFather = childID -- define "top-level" parent node
    FROM   @myTable
    WHERE   parentID = childID
    
    UNION ALL
    
    SELECT a.childID
          ,parentID = CASE 
                           WHEN a.childID=a.parentID THEN -1
                           ELSE a.parentID
                      END
          ,a.[name]
          ,PLevel = cp.PLevel+1
          ,cp.TopLevelFather -- keep selecting the same value for all child nodes
    FROM   @myTable a
            INNER JOIN ChildParent cp  ON  a.parentID = cp.childID
)
SELECT childID
      ,parentID
      ,[name]
      ,PLevel
      ,TopLevelFather
FROM   ChildParent OPTION(MAXRECURSION 100); */

fork query

edited feb 17 13 anonymous

created may 8 12 RoyiNamir - SqlOnline