SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Recursive CTE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/25/2013 :  10:03:32  Show Profile  Reply with Quote
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

547 Posts

Posted - 04/25/2013 :  11:09:33  Show Profile  Reply with Quote

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

Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/26/2013 :  03:10:29  Show Profile  Reply with Quote
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...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 04/26/2013 :  03:55:17  Show Profile  Reply with Quote
--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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  04:03:05  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/26/2013 :  05:21:53  Show Profile  Reply with Quote
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

Edited by - stahorse on 04/26/2013 05:23:21
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  05:23:53  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/26/2013 :  06:21:56  Show Profile  Reply with Quote
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.

Edited by - stahorse on 04/26/2013 06:23:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  06:32:57  Show Profile  Reply with Quote
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
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/26/2013 :  08:43:51  Show Profile  Reply with Quote
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??
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 04/26/2013 :  09:04:58  Show Profile  Reply with Quote
Is this a duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184887 ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  00:41:26  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000