recursive CTE to fetch parent

hi everyone - i am using the following code to get the next immediate parent of a customer in the hierarchy and it works fine. However, it works only for one customer at a time (i made this a scalar function and I pass the customer id as shown below). how can i modify this so that i can get each customer and its next immediate parent (all of them in one shot in a single data set)? thank you in advance!

WITH my_cte (CustomerID, ParentID, Level)
AS
(
SELECT CustomerID, ParentID, 0 AS Level
FROM [dbo].MyTable
WHERE CustomerID = @CustomerID
        
UNION ALL
SELECT CustomerID, ParentID, Level + 1
FROM [dbo].MyTable e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID 
)

select CustomerID from my_cte WHERE Level <> 0 AND Level = (SELECT MIN(Level) FROM my_cte WHERE Level <> 0)

May 20th, 2015 3:20am

how can i modify this so that i can get each customer and its next immediate parent (all of them in one shot in a single data set)? thank you in advance!

Have you tried without WHERE clause : WHERE CustomerID = @CustomerID

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 3:46am

If you only want the next immediate parent, I don't see any need for a recursive CTE. This should do:

SELCET C.CustomerID AS CustID, C.CustomerName,
       P.CustID AS ParentID, P.CustomerName AS ParentName
FROM   Customers C
LEFT   JOIN Customers P C.ParentID = P.CustomerID

May 20th, 2015 6:45am

Try this logic but correct the syntax errors.

;WITH my_cte_1 (CustomerID, ParentID, Level) AS
(
SELECT CustomerID, ParentID, 0 AS Level
FROM [dbo].MyTable
)
,my_cte_2 AS
(
SELECT CustomerID, ParentID, Level + 1
FROM [dbo].MyTable e
INNER JOIN my_cte_1 AS cte 
ON e.CustomerID = cte.ParentID 
)
select CustomerID from my_cte_2 WHERE Level <> 0 AND 
Level = (SELECT MIN(Level) FROM my_cte_2 WHERE Level <> 0)

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 6:47am

If you only want the next immediate parent, I don't see any need for a recursive CTE. This should do:

SELCET C.CustomerID AS CustID, C.CustomerName,
       P.CustID AS ParentID, P.CustomerName AS ParentName
FROM   Customers C
LEFT   JOIN Customers P C.ParentID = P.CustomerID

May 20th, 2015 9:12am

You didn't tell us what the attribute you are looking for is, so the following assumes you are looking for the parent with the value 1 in a column named Status where the Status column cannot be NULL.

WITH my_cte (CustomerID, ParentID, Level) AS ( SELECT CustomerID, ParentID, 0 AS Level FROM [dbo].MyTable UNION ALL SELECT cte.CustomerID, cte.ParentID, cte.Level + 1 FROM [dbo].MyTable e INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID WHERE Level = 0 OR cte.Status <> 1),

cteOrdered As (Select CustomerID, ParentID, Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown From my_cte)

Select CustomerID, ParentID From cteOrdered Where LevelDown = 1;

Tom
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 10:44am

Cause I love rCTEs:

DECLARE @table TABLE (ID INT, parentID INT, likesChinRubs BIT)
INSERT INTO @table (ID, parentID, likesChinRubs) VALUES
(1, NULL, 1),(2,1,0),(3,2,1),(4,3,1),
(5, NULL, 1),(6,5,0),(7,6,1),(8,7,1),(9,7,1)

;WITH rCTE AS (
SELECT ID AS baseID, ID, parentID, likesChinRubs, 0 AS level
  FROM @table
 WHERE parentID IS NULL
UNION ALL
SELECT r.ID AS baseID, a.ID, a.parentID, a.likesChinRubs, level+1
  FROM @table a
    INNER JOIN rCTE r
	  ON a.parentID = r.ID
)


SELECT r1.ID, r2.parentID
  FROM rCTE r1
    INNER JOIN rCTE r2
	  ON r1.ID = r2.ID
	  AND r2.level = (SELECT MAX(level) FROM rCTE WHERE likesChinRubs = 1 AND ID = r2.ID)
 ORDER BY r1.ID, r1.level
likesChinRubs is the columns we're checking.

May 20th, 2015 11:21am

This non-relational model is called an adjacency list tree. You want to use the Nested sets model instead. Google it. Since you did not feel like posting DDL, I do not feel like posting DML. 

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 4:38pm

You didn't tell us what the attribute you are looking for is, so the following assumes you are looking for the parent with the value 1 in a column named Status where the Status column cannot be NULL.

WITH my_cte (CustomerID, ParentID, Level) AS ( SELECT CustomerID, ParentID, 0 AS Level FROM [dbo].MyTable UNION ALL SELECT cte.CustomerID, cte.ParentID, cte.Level + 1 FROM [dbo].MyTable e INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID WHERE Level = 0 OR cte.Status <> 1),

cteOrdered As (Select CustomerID, ParentID, Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown From my_cte)

Select CustomerID, ParentID From cteOrdered Where LevelDown = 1;

Tom

thank you Tom, i spent at least half-day to test this and figure out what is going on but couldn't figure out the issue. below is some sample data and your code. the query never finishes running. please help...

DECLARE @Temp TABLE (CustomerID INT, ParentID INT, Status INT)
INSERT INTO @Temp VALUES (17, 14298, 0)
INSERT INTO @Temp VALUES (20, 17, 0)
INSERT INTO @Temp VALUES (14298, 16475, 1)

;WITH my_cte (CustomerID, ParentID, Level, Status) AS ( SELECT CustomerID, ParentID, 0 AS Level, Status FROM @Temp UNION ALL SELECT cte.CustomerID, cte.ParentID, cte.Level + 1, cte.Status FROM @Temp e INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID WHERE Level = 0 OR cte.Status <> 1), cteOrdered As (Select CustomerID, ParentID, Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown From my_cte) Select CustomerID, ParentID From cteOrdered Where LevelDown = 1 OPTION ( MAXRECURSION 0 )

as per the above dataset, for customer 20 for example, the parent should be 14298 since the status of this member (14298) is 1



May 20th, 2015 5:57pm

Try

;WITH my_cte (CustomerID, ParentID, Level, Status)
AS
(
SELECT CustomerID, ParentID, 0 AS Level, Status
FROM @Temp
        
UNION ALL
SELECT cte.CustomerID, e.ParentID, cte.Level + 1, cte.Status
FROM @Temp e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID
WHERE Level > 0 OR cte.Status <> 1),

cteOrdered As
(Select CustomerID, ParentID,
  Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)

Select CustomerID, ParentID
From cteOrdered
Where LevelDown = 2
OPTION  ( MAXRECURSION 0 )

Tom

Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 7:29pm

thanks again, Tom. any idea why for the below data-set it shows 16475 whereas the correct number should be 14300?

DECLARE @Temp TABLE (CustomerID INT, ParentID INT, Status INT)
INSERT INTO @Temp VALUES (2, 14300, 0)
INSERT INTO @Temp VALUES (14300, 16475, 1)
INSERT INTO @Temp VALUES (16475, NULL, 1)

SELECT * FROM @Temp

;WITH my_cte (CustomerID, ParentID, Level, Status)
AS
(
SELECT CustomerID, ParentID, 0 AS Level, Status
FROM @Temp
        
UNION ALL
SELECT cte.CustomerID, e.ParentID, cte.Level + 1, cte.Status
FROM @Temp e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID
WHERE Level > 0 OR cte.Status <> 1),

cteOrdered As
(Select CustomerID, ParentID,
  Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)

Select CustomerID, ParentID
From cteOrdered
Where LevelDown = 2
OPTION  ( MAXRECURSION 0 )

May 20th, 2015 8:36pm

You didn't tell us what the attribute you are looking for is, so the following assumes you are looking for the parent with the value 1 in a column named Status where the Status column cannot be NULL.

WITH my_cte (CustomerID, ParentID, Level) AS ( SELECT CustomerID, ParentID, 0 AS Level FROM [dbo].MyTable UNION ALL SELECT cte.CustomerID, cte.ParentID, cte.Level + 1 FROM [dbo].MyTable e INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID WHERE Level = 0 OR cte.Status <> 1),

cteOrdered As (Select CustomerID, ParentID, Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown From my_cte)

Select CustomerID, ParentID From cteOrdered Where LevelDown = 1;

Tom

thank you Tom, i spent at least half-day to test this and figure out what is going on but couldn't figure out the issue. below is some sample data and your code. the query never finishes running. please help...

DECLARE @Temp TABLE (CustomerID INT, ParentID INT, Status INT)
INSERT INTO @Temp VALUES (17, 14298, 0)
INSERT INTO @Temp VALUES (20, 17, 0)
INSERT INTO @Temp VALUES (14298, 16475, 1)

;WITH my_cte (CustomerID, ParentID, Level, Status) AS ( SELECT CustomerID, ParentID, 0 AS Level, Status FROM @Temp UNION ALL SELECT cte.CustomerID, cte.ParentID, cte.Level + 1, cte.Status FROM @Temp e INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID WHERE Level = 0 OR cte.Status <> 1), cteOrdered As (Select CustomerID, ParentID, Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown From my_cte) Select CustomerID, ParentID From cteOrdered Where LevelDown = 1 OPTION ( MAXRECURSION 0 )

as per the above dataset, for customer 20 for example, the parent should be 14298 since the status of this member (14298) is 1



Free Windows Admin Tool Kit Click here and download it now
May 20th, 2015 9:53pm

;WITH my_cte (CustomerID, ParentID, GrandParentID, Level, ParentStatus)
AS
(
SELECT e.CustomerID, e.ParentID, p.ParentID As GrandParentID, 0 AS Level, p.Status
FROM @Temp e
INNER JOIN @Temp p On p.CustomerID = e.ParentID
        
UNION ALL
SELECT cte.CustomerID, cte.GrandParentID, e.ParentID, cte.Level + 1, e.Status
FROM @Temp e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.GrandParentID
WHERE cte.ParentStatus <> 1),

cteOrdered As
(Select CustomerID, ParentID, Level, ParentStatus,
  Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)

Select CustomerID, ParentID
From cteOrdered
Where ParentStatus = 1 And LevelDown = 1
Order By CustomerID, Level
OPTION  ( MAXRECURSION 0 )

Tom
May 20th, 2015 11:48pm

;WITH my_cte (CustomerID, ParentID, GrandParentID, Level, ParentStatus)
AS
(
SELECT e.CustomerID, e.ParentID, p.ParentID As GrandParentID, 0 AS Level, p.Status
FROM @Temp e
INNER JOIN @Temp p On p.CustomerID = e.ParentID
        
UNION ALL
SELECT cte.CustomerID, cte.GrandParentID, e.ParentID, cte.Level + 1, e.Status
FROM @Temp e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.GrandParentID
WHERE cte.ParentStatus <> 1),

cteOrdered As
(Select CustomerID, ParentID, Level, ParentStatus,
  Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)

Select CustomerID, ParentID
From cteOrdered
Where ParentStatus = 1 And LevelDown = 1
Order By CustomerID, Level
OPTION  ( MAXRECURSION 0 )

Tom
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 3:44am

;WITH my_cte (CustomerID, ParentID, GrandParentID, Level, ParentStatus)
AS
(
SELECT e.CustomerID, e.ParentID, p.ParentID As GrandParentID, 0 AS Level, p.Status
FROM @Temp e
INNER JOIN @Temp p On p.CustomerID = e.ParentID
        
UNION ALL
SELECT cte.CustomerID, cte.GrandParentID, e.ParentID, cte.Level + 1, e.Status
FROM @Temp e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.GrandParentID
WHERE cte.ParentStatus <> 1),

cteOrdered As
(Select CustomerID, ParentID, Level, ParentStatus,
  Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)

Select CustomerID, ParentID
From cteOrdered
Where ParentStatus = 1 And LevelDown = 1
Order By CustomerID, Level
OPTION  ( MAXRECURSION 0 )

Tom
May 21st, 2015 3:44am

Good day SnowShine429,

Is this still an issue?
If you got the answer then please close the thread by marking the answer/answers

Thanks


Free Windows Admin Tool Kit Click here and download it now
May 30th, 2015 11:42am

I can't start with "Good day", which i am not sure that I want to bless you while you hurts people, so I will start with "Hello Celko"

I deleted your other response AGAIN, after it was reported as abusive AGAIN! I dont have time to edit your message AGAIN, at this time in order to clean it.

1. There is only one rude person in the forum, and that is not newbie that came to ask a question, and did not how to do so in the best way, but you! calling people by names and cursing people is rudence! Moreover, it harts the forum.

Sentences like "Since you are rude, ignorant or both" are not acceptable!

2. Moreover, you are using the forum only to sell you books and sentences like "I have a book... which you can get at..." are pure advertisements!

May 31st, 2015 2:01am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics