SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


While Loop/ CTE Statement


While Loop/ CTE Statement

Author
Message
hoseam
hoseam
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 431
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
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 3318
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search