Recursive CTE

Question

Post reply

Recursive CTE

hoseam

SSCertifiable

Points: 5533
More actions
April 25, 2013 at 8:03 am

#277022

Hi,
I have a query below I'm doing recursive CTE.
The logic behind it is, Client(John) can be linked another Client(Mary). And Mary can also be linked to another Client(Smith). All this clients has Client Numbers. Now when a user input a Client's Number let's say (1-23) which is John's, the query supposed to return all Clients linked to John, and if those Clients are linked to other Clients, show them as well. I hope it makes sense.
Now the problem I have is, when I input Mary's client Id, which is (1-33), I get all clients linked to her and other clients in the linking chain, except for one client linked to John(1-23), that client is Diana(1-14)
I hope I've been able to explain this very well. Can one spot out my error and help me.
Below is my code to attempt it:
--create PR_LINK_INV_HST temp table--
Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))
--insert into PR_LINK_INV_HST temp table--
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-14')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-33')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana',null)
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-19')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith',null)
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-16')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-17')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo',null)
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-50','Josh','1-23')
DECLARE @CLIENT_ID VARCHAR(15)
SET @CLIENT_ID = '1-33'
-- This CTE search for the linked client(child)--
;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(
SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 1
FROM #PR_LINK_INV_HST
WHERE LINK_CLIENT_ID = @CLIENT_ID
UNION ALL
SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1
FROM #PR_LINK_INV_HST HST
JOIN pr_linked LNK
ON LNK.CLIENT_ID = HST.LINK_CLIENT_ID
)
SELECT *
INTO #RESULTS
FROM pr_linked
-- This CTE search upwards for the linked client(parent)--
;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(
SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0
FROM #PR_LINK_INV_HST
WHERE CLIENT_ID = @CLIENT_ID
UNION ALL
SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] - 1
FROM #PR_LINK_INV_HST HST
JOIN pr_linked LNK
ON HST.CLIENT_ID = LNK.LINK_CLIENT_ID
)
INSERT INTO #RESULTS
SELECT *
FROM pr_linked
-- display result
SELECT *
FROM #RESULTS
drop table #RESULTS
drop table #PR_LINK_INV_HST

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try this...

-- This CTE search for the linked client(child)--

;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])

AS

(

SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 1

FROM #PR_LINK_INV_HST

--WHERE LINK_CLIENT_ID = @CLIENT_ID

WHERE CLIENT_ID = @CLIENT_ID

^{“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

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

I made the following tweaks to your CTE to give what I hope is a better result:

1. Fixed the main problem, which was that the anchor member of the "child" CTE had this: WHERE LINK_CLIENT_ID = @CLIENT_ID but it should have had WHERE CLIENT_ID = @CLIENT_ID.

2. I changed the "LEVEL" value of the anchor member from 1 to 0. That way numbers greater than 0 are clearly "child" rows, and numbers less than 0 are "parent" rows. 0 is the client himself or herself.

2. Consolidated the two CTEs into one CTE with two recursive members: the first one finds the "child" clients, and the second recursive member files the "parent" clients. I have had a hard time thinking of a good use of multiple recursive members, but you found one: searching both down and up from any given point in a hierarchy.

3. Added DISTINCT to the insertion into the #Results table, since the results could contain duplicate rows by the nature of the recursive search.

4. Added and ORDER BY to the final SELECT in order to see the hierarchy in sequence from top to bottom.

--create PR_LINK_INV_HST temp table--

Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))

--insert into PR_LINK_INV_HST temp table--

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-14')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-33')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana',null)

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-19')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith',null)

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-16')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-17')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo',null)

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-50','Josh','1-23')

DECLARE @CLIENT_ID VARCHAR(15)

SET @CLIENT_ID = '1-33'

-- This CTE search for the linked clients --

;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])

AS

(

/* Anchor member - the selected client*/

SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0

FROM #PR_LINK_INV_HST

WHERE CLIENT_ID = @CLIENT_ID

UNION ALL

/* Recursive member to search for the child clients. */

SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1

FROM #PR_LINK_INV_HST HST

JOIN pr_linked LNK

ON LNK.LINK_CLIENT_ID = HST.CLIENT_ID

where

lnk.LEVEL >= 0

/* Recursive member to search for the parent clients. */

UNION ALL

SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] - 1

FROM #PR_LINK_INV_HST HST

JOIN pr_linked LNK

ON LNK.CLIENT_ID = HST.LINK_CLIENT_ID

where lnk.LEVEL <= 0

)

SELECT distinct *

INTO #RESULTS

FROM pr_linked

-- display result

SELECT *

FROM #RESULTS

order by LEVEL, NAME

drop table #RESULTS

drop table #PR_LINK_INV_HST

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Nice one, Geoff! Other than swapping around the sign on the hierarchy direction, it's bang on.

^{“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

hoseam SSCertifiable Points: 5533 More actions · Answer 4

Thank you guys very much. I appreciate all your help a lot.

I have one question, If I input client 1-33, which is Mary, shouldn't I get, in my results set, client 1-14 which is Diana because she is also linked to John? John who is linked to Mary?

geoff5 Hall of Fame Points: 3560 More actions · Answer 5

First, let me point out that I understand "Link_Client_ID" to mean "the client of the current person" and not the other way around. In other words the first row of your sample data indicates that Diana is the client of John and not that John is the client of Diana. Read in this way, Josh is the "top" of the hierarchy and "Thabo" is the very bottom of it. Thus the various permutations are : "Josh - John - Diana," "Josh - John - Mary - Smith," and "Josh - John - Mary - Pope - Thabo."

Having established the "up" and "down" direction, let's consider the effect of direction on the recursive query with regard to "Mary." For the recursive query that is moving down the tree from Mary to the bottom, it includes all the branches that "sprout" from the Mary node. However, for the recursive query that is moving up the tree from Mary to the top, it only includes the one branch that moves directly up from Mary to the top; it does not include any "parallel" branches.

I guess another way to look at this is as a "family tree." When flowing down, the tree only includes descendants of Mary. When flowing up, the tree only includes the ancestors of Mary. In the family tree metaphor, Diana is a "sister" of Mary, neither a descendant nor an ancestor, and so she does not show up.

If you want to include Diana and any other possible "sisters" or "cousins" of Mary, then what you probably want to do is a two step approach: First, starting from Mary, locate the "top" of the tree (Josh). Second, locate all of the descendants from the "top" record discovered in the first step.

This would require two recursive CTEs: the first one looking "up" from Mary to the top of her tree, and the second one looking "down" from whatever top record was identified in the first CTE.

geoff5 Hall of Fame Points: 3560 More actions · Answer 6

Here is a new query with two CTEs: the first finds all the ancestors of the selected client; the second CTE finds all the descendants of the top ancestor in the first CTE results. I added a column to show the "ClientOf" value explicitly. Note also that any name at Level 0 along with the selected client is a "peer" or "sibling" of the selected client in the tree structure.