Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

86 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

4614 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

4614 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  
 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.06 seconds. Powered By: Snitz Forums 2000