SQL Server CTE -Find top parentID forEach childID?

Question

I have a table which contains hierarchy data - something like:

childID  |  parentID
____________________
  1      |     5
  5      |     9
  9      |     20
  2      |     4
  3      |     7
  7      |     8
  8      |     8
 20      |     20
  4      |     4
  8      |     8

desired output:

enter image description here

I've created a recursive CTE which finds me the top fatherID.

Something like:

;WITH cte AS (
                 SELECT a.childID
                       ,a.parentID
                       ,1 AS lvl
                 FROM   [Agent_Agents] a
                 WHERE   a.childID = 214 //<==== value to begin with !! - thats part the problem
                 UNION ALL
                 SELECT tmp.childID
                       ,tmp.parentID
                       ,cte.lvl+1
                 FROM   [Agent_Agents] tmp
                         INNER JOIN cte  ON  tmp.childID = cte.parentID
                 WHERE   cte.childID<>cte.parentID
             )
SELECT *
FROM   cte
WHERE   lvl = (
            SELECT MAX(lvl)
            FROM   cte
        )

The problem:

I executed the CTE with explicit childID value to begin with (214) ! So it gives me the value for 214 only. the CTE do the recursive part and find topParent for childID.

but I want ForEach row in the Table - to execute the CTE with the childID value !

I have tried to do it with CROSS APPLY:

Something like:

select * from myTable Cross Apply (
                                     ;WITH cte AS (....)
                                  )

but IMHO (from my testing !!) - its impossible.

The other idea of putting the recursive CTE in a UDF has a performance penalty (udf's problem as we know).

How can I create this query so that it'll actually work? ( or some near solution )?

here is what I've tried

https://data.stackexchange.com/stackoverflow/query/edit/69458

marc_s · Answer 1 · 2012-05-08T11:27:33.520

Can't you do something like this?

;WITH cte AS (....)
SELECT
    * 
FROM 
    cte
CROSS APPLY 
    dbo.myTable tbl ON cte.XXX = tbl.XXX

Put the CROSS APPLY after the CTE definition - into the one SQL statement that refers back to the CTE. Wouldn't that work??

OR: - flip around your logic - do a "top-down" CTE, that picks the top-level nodes first, and then iterates through the hiearchy. This way, you can easily determine the "top-level father" in the first part of the recursive CTE - something like this:

;WITH ChildParent AS
(
    SELECT
        ID,
        ParentID = ISNULL(ParentID, -1),
        SomeName, 
        PLevel = 1,   -- defines level, 1 = TOP, 2 = immediate child nodes etc.
        TopLevelFather = ID  -- define "top-level" parent node
    FROM dbo.[Agent_Agents] 
    WHERE ParentID IS NULL

    UNION ALL

    SELECT
        a.ID,
        ParentID = ISNULL(a.ParentID, -1),
        a.SomeName, 
        PLevel = cp.PLevel + 1,
        cp.TopLevelFather   -- keep selecting the same value for all child nodes
    FROM dbo.[Agent_Agents] a
    INNER JOIN ChildParent cp ON r.ParentID = cp.ID
)
SELECT  
   ID,
   ParentID,
   SomeName,
   PLevel,
   TopLevelFather   
FROM ChildParent

This would give you nodes something like this (based on your sample data, slightly extended):

ID  ParentID  SomeName      PLevel  TopLevelFather
20    -1      Top#20           1          20
 4    -1      TOP#4            1           4
 8    -1      TOP#8            1           8
 7     8      ChildID = 7      2           8
 3     7      ChildID = 3      3           8
 2     4      ChildID = 2      2           4
 9    20      ChildID = 9      2          20
 5     9      ChildID = 5      3          20
 1     5      ChildID = 1      4          20

Now if you select a particular child node from this CTE output, you'll always get all the infos you need - including the "level" of the child, and its top-level parent node.

I dont think it will wiork - since CTE knows how to execute and find one(!!) top father for ID. notice the 214 value..... how will i send Foreach ID => Cte.DoWorkFor(ID) ? — Royi Namir, May 08 '12 at 11:41
@RoyiNamir: with this `ChildParent` CTE - can't you just do `SELECT * FROM ChildParent WHERE ID = 214` and get what you need?? — marc_s, May 08 '12 at 11:55

score 17 · Accepted Answer · edited Aug 29 '17 at 13:00

17

Not sure I understand what you are looking for but it could be this.

;WITH c 
     AS (SELECT childid, 
                parentid, 
                parentid AS topParentID 
         FROM   @myTable 
         WHERE  childid = parentid 
         UNION ALL 
         SELECT T.childid, 
                T.parentid, 
                c.topparentid 
         FROM   @myTable AS T 
                INNER JOIN c 
                        ON T.parentid = c.childid 
         WHERE  T.childid <> T.parentid) 
SELECT childid, 
       topparentid 
FROM   c 
ORDER  BY childid

SE-Data

It is the same as answer by marc_s with the difference that I use your table variable and the fact that you have childID = parentID for root nodes where the answer by marc_s has parent_ID = null for root nodes. In my opinion it is better to have parent_ID = null for root nodes.

edited Aug 29 '17 at 13:00

Cœur

37,241
25
195
267

answered May 08 '12 at 13:28

Mikael Eriksson

136,425
22
210
281

ive added a print screen for the desired output. – Royi Namir May 08 '12 at 13:37
@RoyiNamir - My query on SE-Data returns what you want. I just added the `name` and `parentID` columns. – Mikael Eriksson May 08 '12 at 13:42
Ill be GLAD to hear why you decided to start with the topmost - and not the leafs .... what is the logic here (although its working) ? why couldn't we start with the leafs towards topmost leafs ? – Royi Namir May 08 '12 at 14:54
@RoyiNamir Going from the leaf node will add more rows to the result set than you need. If one parent has three children you will have that parent in the result set three times. Going from top to bottom in the recursive query you only add the new nodes you find one time for each node. – Mikael Eriksson May 08 '12 at 15:02
you said : "_If one parent has three children you will have that parent in the result set three times_" - but accroding to my question - you should see it 3 times. no ?if 1 has parent 2 , and 5 has parent 2 , and 9 has parent 2 – you should see 1,2 & 5,2 & 9,2 .... no ? – Royi Namir Dec 09 '14 at 17:28
@RoyiNamir I meant that you will have the parent three times in the result set like [this](http://data.stackexchange.com/stackoverflow/query/256601) if you get the leaf nods in the achor. – Mikael Eriksson Dec 10 '14 at 15:01

score 1 · Answer 3 · answered May 08 '12 at 13:25

1

I have not yet the time to look further into your question and am not sure whether or not i've understood your problem, but couldn't you use this svf to get the top father's id?

CREATE FUNCTION [dbo].[getTopParent] (
    @ChildID INT
)

RETURNS int
AS
BEGIN
    DECLARE @result int;
    DECLARE @ParentID int;

    SET @ParentID=(
        SELECT ParentID FROM ChildParent
        WHERE ChildID = @ChildID 
    )

    IF(@ParentID IS NULL)
        SET @result = @ChildID 
    ELSE
        SET @result = [dbo].[getTopParent](@ParentID)

    RETURN @result    
END

Then you should be able to find each top parent in this way:

SELECT ChildID
    ,  [dbo].[getTopParent](ChildID) AS TopParentID
FROM ChildParent

answered May 08 '12 at 13:25

Tim Schmelter

450,073
74
686
939

Thanks for answering. tim. the problem ( as we know ) tha t udf reduces performance ( while inline calling...) thats why i tend to avoid this kind of solution. the problem ( in general) : i have table with `id` and `parentId`. foreach line in the (select * from ...) - i need another column which has the topmost father value.- ive tried with Cross Aply - vut the recursive engine ( CTE) cant be used with Cross Apply). – Royi Namir May 08 '12 at 13:28
ive added a print screen to the desired output. – Royi Namir May 08 '12 at 13:36

score 0 · Answer 4 · edited Jan 23 '13 at 14:02

select distinct 
       a.ChildID,a.ParentID,
       --isnull(nullif(c.parentID,b.parentID),a.parentID) as toppa,
       B.parentID
       --,c.parentID
      ,isnull(nullif(d.parentID,a.parentID),c.parentID) as toppa1,a.name
from myTable a
   inner join myTable c
       on a.parentID=c.parentID
   inner join myTable b
       on b.childID=a.parentID
   inner join myTable d
       on d.childID=b.parentID

I have using the without CTE expression and then using joins to get the step to step parent for child and then more important Common table expressions were introduced in SQL Server 2005 not in server 2000 so using joins to get values this is basic way for to get parentid for a child value

score 0 · Answer 5 · edited Nov 15 '16 at 09:36

select dbo.[fn_getIMCatPath](8)
select Cat_id,Cat_name,dbo.[fn_getIMCatPath](cat_id) from im_category_master

Create FUNCTION [dbo].[fn_getIMCatPath] (@ID INT) 
returns NVARCHAR(1000) 
AS 
BEGIN 
  DECLARE @Return   NVARCHAR(1000), 
          @parentID INT, 
          @iCount   INT 

  SET @iCount = 0 

  SELECT @Return = Cat_name, 
         @parentID = parent_id 
  FROM   im_category_master 
  WHERE  [cat_id] = @ID 

  WHILE @parentID IS NOT NULL 
    BEGIN 
        SELECT @Return = cat_name + '>' + @Return, 
               @parentID = parent_id 
        FROM   im_category_master 
        WHERE  [cat_id] = @parentID 

        SET @iCount = @iCount + 1 
        IF @parentID = -1
        BEGIN
        SET @parentID = NULL 
        END
        IF @iCount > 10 
          BEGIN 
              SET @parentID = NULL 
              SET @Return = '' 
          END 
    END 

  RETURN @Return 
END

BethT · Answer 6 · 2017-07-07T09:21:57.683

Consider this sample data and respective SQL to access child records along with their top parent.

Sample DATA

SQL code:

;WITH c AS (
   SELECT Id, Name, ParentId as CategoryId, 
          Id as MainCategoryId, Name AS MainCategory 
     FROM   pmsItemCategory 
     WHERE  ParentId is null

     UNION ALL 

     SELECT T.Id, T.Name, T.ParentId,  MainCategoryId, MainCategory 
     FROM   pmsItemCategory AS T 
            INNER JOIN c  ON T.ParentId = c.Id 
     WHERE  T.ParentId is not null
    ) 

SELECT Id, Name, CategoryId, MainCategoryId, MainCategory 
FROM   c 
order by Id

score -1 · Answer 7 · edited Jan 22 '13 at 13:26

-1

select distinct 
       a.ChildID,a.ParentID,
       --isnull(nullif(c.parentID,b.parentID),a.parentID) as toppa,
       B.parentID
       --,c.parentID
      ,isnull(nullif(d.parentID,a.parentID),c.parentID) as toppa1,a.name
from myTable a
   inner join myTable c
       on a.parentID=c.parentID
   inner join myTable b
       on b.childID=a.parentID
   inner join myTable d
       on d.childID=b.parentID

edited Jan 22 '13 at 13:26

Sled

18,541
27
119
168

answered Jan 22 '13 at 13:09

Bharani

1
1

please explain your answer instead of just giving it – Sled Jan 22 '13 at 13:26
I have using the without CTE expression and then using joins to get the step to step parent for child and then more important Common table expressions were introduced in SQL Server 2005 not in server 2000 so using joins to get values this is basic way for to get parentid for a child value – Bharani Jan 23 '13 at 10:53

score -1 · Answer 8 · edited Mar 13 '15 at 11:37

-1

With cte as 
(
Select ChileId,Name,ParentId from tblHerarchy
where ParentId is null 
union ALL
Select h.ChileId,h.Name,h.ParentId  from cte
inner join tblHerarchy h on h.ParentId=cte.ChileId
) 
Select * from cte

edited Mar 13 '15 at 11:37

HaveNoDisplayName

8,291
106
37
47

answered Mar 13 '15 at 11:23

user4667013

1

score -1 · Answer 9 · edited Mar 13 '15 at 11:49

-1

With cteherarchy as 
(
Select ChileId,Name,ParentId from tblHerarchy
where ParentId is null 
union ALL
Select h.ChileId,h.Name,h.ParentId  from cte
inner join tblHerarchy h on h.ParentId=cte.ChileId
) 
Select * from cteherarchy

edited Mar 13 '15 at 11:49

Opal

81,889
28
189
210

answered Mar 13 '15 at 11:28

sunny singla

11
1

This solution doesn't answer the question. Original data doesn't have `NULL` in `ParentID`. The suggested query doesn't produce the desired output. – Vladimir Baranov Mar 15 '15 at 23:13

SQL Server CTE -Find top parentID forEach childID?

9 Answers9

Linked

Related