Recursive CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Recursive CTE

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-25 : 10:03:32

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 11:09:33

Since you are starting you query with LINK_CLIENT_ID = '1-33'; the LINK_CLIENT_ID '1-14' of John is never included in your search.

If you modify your query as shown in red below you will have Diana in your output. You may endup with lot of duplicates, you may want to filter the duplicates...

quote:
Originally posted by stahorse

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'

DECLARE @CLIENT_ID1 VARCHAR(15)
SET @CLIENT_ID1 = '1-14'

-- 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 =in (@CLIENT_ID, @CLIENT_ID1)

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 =in (@CLIENT_ID, @CLIENT_ID1)

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

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-26 : 03:10:29

Hi

My Client_ID don't necessarily has to start with 1-33(Mary), but If they wanted to search for Mary and anyone who is linked to her, and others who are linked to people Mary is linked to, shouldn't 1-14(Diana) also show in my result set? because she is linked to John(1-23) who is linked to Mary...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-26 : 03:55:17

--This?


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 LINK_CLIENT_ID, Name, 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.LINK_CLIENT_ID= HST.CLIENT_ID  --HST.CLIENT_ID = LNK.LINK_CLIENT_ID

)
SELECT * 
INTO #RESULTS
FROM pr_linked

select * from #RESULTS ORDER BY LEVEL

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 04:03:05

As per your sample data Diana is not linked to anyone as her LINK_CLIENT_ID is having NULL value so hence she wont be returned in the output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-26 : 05:21:53

I Edited this query below, please run it and see. it works as I expect it to except, Diana(1-14) has null value on LINK_CLIENT_ID, but John is linked to her, she is John's LINK_CLIENT_ID, so should I also get her in the out put, because when I search for Mary I should get anyone related to her, and people related to her people?

--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

/* Recursive member to search for the child 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.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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 05:23:53

yep...that was my point
if you want get her also you need to do the recursion in both directions ( which is exactly what you did in your two recursive sections above)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-26 : 06:21:56

yes but I'm still not getting her, if you run that code you will see that I get all the people who are directly and indirently linked to Mary, except her.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 06:32:57

thats because the below record for john

'1-23','John','1-14'

will not get included as you link by means of Marys ClientID '1-33' hence it cant link back to Diana as you expect

The only solution is to add LINKID for Diana from NULL to Marys or any of Mary's related clients ids

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-26 : 08:43:51

I now have this table,

--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','John','2')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('2','John','3')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('3','Diana','1')

with the same query as above, how do I escape the "The maximum recursion 100 has been exhausted" error??

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 09:04:58

Is this a duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184887 ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:41:26

quote:
Originally posted by stahorse

I now have this table,

--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','John','2')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('2','John','3')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('3','Diana','1')

with the same query as above, how do I escape the "The maximum recursion 100 has been exhausted" error??

You need to avoid cross references in your recusrive part of CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources