Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursive CTE Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 8:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
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
Post #1446511
Posted Thursday, April 25, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 7,175, Visits: 13,618
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446545
Posted Thursday, April 25, 2013 8:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:00 AM
Points: 169, Visits: 493
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

Post #1446546
Posted Thursday, April 25, 2013 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 7,175, Visits: 13,618
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446549
Posted Friday, April 26, 2013 12:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
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?
Post #1446816
Posted Friday, April 26, 2013 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:00 AM
Points: 169, Visits: 493
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.
Post #1447041
Posted Friday, April 26, 2013 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:00 AM
Points: 169, Visits: 493
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.

--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 searches for the "ancestors" of the selected client. --
;WITH client_ancestors (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

/* 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 client_ancestors LNK
ON LNK.CLIENT_ID = HST.LINK_CLIENT_ID
where lnk.LEVEL <= 0
),

-- This CTE searches for all the "descendants" of the top of the tree that includes the selected client.
all_connected_clients as
(
/* Anchor member is the top of the tree identified in the treetop CTE. */
SELECT CLIENT_ID, Name, LINK_CLIENT_ID, cast(null as varchar(50)) as ClientOf, (select min(Level) from client_ancestors) as Level
FROM #PR_LINK_INV_HST
WHERE CLIENT_ID = (select top 1 CLIENT_ID from client_ancestors order by Level)

UNION ALL
/* Recursive member to search for the child clients. */
SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, cast(Lnk.Name as varchar(50)) as ClientOf, LNK.[LEVEL] + 1
FROM #PR_LINK_INV_HST HST
JOIN all_connected_clients LNK
ON LNK.LINK_CLIENT_ID = HST.CLIENT_ID )

select distinct CLIENT_ID, NAME, ClientOf, Level from all_connected_clients
order by Level, NAME


drop table #PR_LINK_INV_HST

Post #1447074
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse