April 23, 2013 at 9:22 am
Hi
I have a table, PR_LINK_INV_HST, and I need to find all the records on PR_LINK_INV_HST where the “Client (CSN)” = PR_LINK_INV_HST.CLIENT_ID or PR_LINK_INV_HST.LINKED_CLIENT_ID. and return all LINK_CLIENT_ID’s and CLIENT_ID’s for the specified “Client (CSN)”. then continue looping through the PR_LINK_INV_HST table to also find the clients that are linked to the linked clients of the specified “Client (CSN)”.
so this is my table PR_LINK_INV_HST(CLIENT_ID, LINK_CLIENT_ID)
Can anyone help help me on how to get this right because the query I have gives me an infinite loop.
My Query:
Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','linked to Diana (1-14)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','linked to Mary (1-33)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana','not linked')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','linked to Smith (1-16)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','linked to Pope (1-17)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith','not linked')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','linked to Thabo (1-19)')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo','not linked')
DECLARE @CLIENT_ID VARCHAR(15)
SET @CLIENT_ID = '1-23'
;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.CLIENT_ID
)
SELECT *
INTO #RESULTS
FROM pr_linked
select * from #RESULTS
April 23, 2013 at 1:59 pm
Hi
The problem appears to be that you are joining in CLIENT_ID to CLIENT_ID in your recursive query rather than HST.CLIENT_ID to LNK.LINK_CLIENT_ID.
I'm assuming that the extra descriptive text in the LINK_CLIENT_ID is not in the real data
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply