cte - finding for each child - its top parent.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Quality Assurance and Testing Meta

Q&A about the site for software quality control experts, automation engineers, and software testers

-- 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); */

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results