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)
 While Loop/Recursive Statements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/23/2013 :  11:21:12  Show Profile  Reply with Quote
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

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 04/23/2013 :  12:16:02  Show Profile  Reply with Quote
The infinate loop is becuase you are matching ClientID to ClientID.. So you really want to join the ClientID to a portion of the LinkID or vise versa?

Can you show your expected results or explain with data. For example, given teh results of the anchor query:
CLIENT_ID       NAME            LINK_CLIENT_ID                 LEVEL
--------------- --------------- ------------------------------ -----------
1-23            John            linked to Diana (1-14)         0
1-23            John            linked to Mary (1-33)          0

What do you want for the next level?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 04/23/2013 :  12:19:08  Show Profile  Reply with Quote
Just a guess, if this isn't right, plese see my post above:
;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 = LEFT(RIGHT(LNK.LINK_CLIENT_ID, 5), 4)

)

select * from pr_linked
PS: Any chance you can fix you data structure?
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.05 seconds. Powered By: Snitz Forums 2000