CTE Recursive

Question

CTE Recursive

hoseam

SSCertifiable

Points: 5533
More actions
May 9, 2013 at 2:56 am

#277510

Hi
I have a query below and I will explain what I wish to archive. I have two methods that I need to do, (1)find all the records on #PR_LINK_INV_HST where the @Client_Id = #PR_LINK_INV_HST.CLIENT_ID or #PR_LINK_INV_HST.LINKED_CLIENT_ID (2) find the clients that are linked to the linked clients of the specified Client, we call this “deep linking”.
All the clients will be on CLIENT_ID, and the clients they are linked to will be on LINKED_CLIENT_ID. Now e.g, if our specified client is Client A we will find client A(CLIENT_ID) is linked to client B(LINKED_CLIENT_ID), if client B(LINKED_CLIENT_ID) is also a child to client C(CLIENT_ID), we must also show this row because client C is indirectly linked to A because of client B.
below is my query, whih somehow my logic is wrong, because when I select LINK_CLIENT_ID only on my finaly DISTINCT SELECT, I get 6 rows back, which is correct, but when I select all three colums I get 14 back, which is wrong, I have to get only 6 rows.
Please help.
--create PR_LINK_INV_HST temp table--
Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(50), LINK_CLIENT_ID varchar(50), LINK_REASON varchar(50))
--insert into PR_LINK_INV_HST temp table--
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('1-1VYON9','2-85Z35','CIVIL_PARTNERS')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-4NH3J','2-85Z35','UNDERLYING_CLNT')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-85Z35','2-4NH3J','CIVIL_PARTNERS')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-4NH3J','UNDERLYING_CLNT')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-7Z7IJ','UNDERLYING_CLNT')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-85Z35','HUSBAND_AND_WIFE')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-D0HF1','2-85Z35','CIVIL_PARTNERS')
insert into #PR_LINK_INV_HST
Select LINK_CLIENT_ID,CLIENT_ID,LINK_REASON
FROM #PR_LINK_INV_HST
declare @CLIENT_ID VARCHAR(10) set @CLIENT_ID = '1-1VYON9'
;WITH cte AS
(
SELECT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON, CAST(CLIENT_ID + '/' AS VARCHAR(MAX)) AS traversed
FROM #PR_LINK_INV_HST
WHERE @CLIENT_ID = CLIENT_ID
UNION ALL
SELECT t.CLIENT_ID, t.LINK_CLIENT_ID, t.LINK_REASON, CAST(traversed+t.client_id + '/' AS VARCHAR(MAX)) AS traversed
FROM #PR_LINK_INV_HST t
INNER JOIN cte c ON c.LINK_CLIENT_ID = t.CLIENT_ID
WHERE traversed NOT LIKE '%'+t.client_id + '%'
)
SELECT DISTINCT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON FROM cte
drop table #PR_LINK_INV_HST

Viewing 2 posts - 1 through 1 (of 1 total)

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

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

Your data set is causing heaps of problems here - there are associations where there shouldn't be. I think the query is ok, in fact with properly formed data, you probably won't need the WHERE clause in the recursive part:

--create PR_LINK_INV_HST temp table--

DROP TABLE #PR_LINK_INV_HST

Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(50), LINK_CLIENT_ID varchar(50), LINK_REASON varchar(50))

--insert into PR_LINK_INV_HST temp table--

Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('1-1VYON9','2-85Z35','CIVIL_PARTNERS')

Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-4NH3J','2-85Z35','UNDERLYING_CLNT')

--Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-85Z35','2-4NH3J','CIVIL_PARTNERS')

Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-4NH3J','UNDERLYING_CLNT')

Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-7Z7IJ','UNDERLYING_CLNT')

Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-85Z35','HUSBAND_AND_WIFE')

Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-D0HF1','2-85Z35','CIVIL_PARTNERS')

insert into #PR_LINK_INV_HST

Select LINK_CLIENT_ID,CLIENT_ID,LINK_REASON

FROM #PR_LINK_INV_HST

declare @CLIENT_ID VARCHAR(10) set @CLIENT_ID = '1-1VYON9'

;WITH cte AS

(

SELECT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON,

[Level] = 1,

traversed = CAST(CLIENT_ID AS VARCHAR(8000))

FROM #PR_LINK_INV_HST

WHERE CLIENT_ID = @CLIENT_ID

UNION ALL

SELECT t.CLIENT_ID, t.LINK_CLIENT_ID, t.LINK_REASON,

[Level] = c.[Level] + 1,

traversed = CAST(c.traversed + ' / ' + t.client_id AS VARCHAR(8000))

FROM #PR_LINK_INV_HST t

INNER JOIN cte c

ON c.LINK_CLIENT_ID = t.CLIENT_ID

-- probably not required with well-formed data

WHERE c.traversed NOT LIKE '%'+t.client_id + '%'

)

SELECT *

FROM cte

ORDER BY [Level], CLIENT_ID

OPTION (MAXRECURSION 0)

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