Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

While Loop/ CTE Statement Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:39 AM
Points: 170, Visits: 269
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

Post #1445519
Posted Tuesday, April 23, 2013 1:59 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:07 PM
Points: 953, Visits: 2,897
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

Post #1445672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse