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)
 CTE Recursive
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

86 Posts

Posted - 05/09/2013 :  04:55:46  Show Profile  Reply with Quote
Hi

I have a query below and I will explain what I wish to archive. I have two methods that I need to do, (1)find all the records on #PR_LINK_INV_HST where the @Client_Id = #PR_LINK_INV_HST.CLIENT_ID or #PR_LINK_INV_HST.LINKED_CLIENT_ID (2) find the clients that are linked to the linked clients of the specified Client, we call this “deep linking”.

All the clients will be on CLIENT_ID, and the clients they are linked to will be on LINKED_CLIENT_ID. Now e.g, if our specified client is Client A we will find client A(CLIENT_ID) is linked to client B(LINKED_CLIENT_ID), if client B(LINKED_CLIENT_ID) is also a child to client C(CLIENT_ID), we must also show this row because client C is indirectly linked to A because of client B.

on my finaly DISTINCT SELECT, I get 6 rows back, which is correct, but when I select all three colums I get 14 back, which is wrong, I have to get only 6 rows.

Please help.

--create PR_LINK_INV_HST temp table--
Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(50), LINK_CLIENT_ID varchar(50), LINK_REASON varchar(50))

--insert into PR_LINK_INV_HST temp table--
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('1-1VYON9','2-85Z35','CIVIL_PARTNERS')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-4NH3J','2-85Z35','UNDERLYING_CLNT')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-85Z35','2-4NH3J','CIVIL_PARTNERS')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-4NH3J','UNDERLYING_CLNT')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-7Z7IJ','UNDERLYING_CLNT')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-CN1EO','2-85Z35','HUSBAND_AND_WIFE')
Insert into #PR_LINK_INV_HST (CLIENT_ID,LINK_CLIENT_ID,LINK_REASON) values('2-D0HF1','2-85Z35','CIVIL_PARTNERS')

insert into #PR_LINK_INV_HST
Select LINK_CLIENT_ID,CLIENT_ID,LINK_REASON
FROM #PR_LINK_INV_HST

declare @CLIENT_ID VARCHAR(10) set @CLIENT_ID = '1-1VYON9'

;WITH cte AS
(
SELECT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON, CAST(CLIENT_ID + '/' AS VARCHAR(MAX)) AS traversed
FROM #PR_LINK_INV_HST
WHERE @CLIENT_ID = CLIENT_ID

UNION ALL

SELECT t.CLIENT_ID, t.LINK_CLIENT_ID, t.LINK_REASON, CAST(traversed+t.client_id + '/' AS VARCHAR(MAX)) AS traversed
FROM #PR_LINK_INV_HST t
INNER JOIN cte c ON c.LINK_CLIENT_ID = t.CLIENT_ID
WHERE traversed NOT LIKE '%'+t.client_id + '%'
)

SELECT DISTINCT CLIENT_ID, LINK_CLIENT_ID, LINK_REASON FROM cte

drop table #PR_LINK_INV_HST

Edited by - stahorse on 05/09/2013 04:59:41

MuMu88
Aged Yak Warrior

549 Posts

Posted - 05/09/2013 :  11:36:54  Show Profile  Reply with Quote
Hi,
You are getting 14 different rows because for each CLIENT_ID, the values for LINK_CLIENT_ID and LINK_REASON are different.
If you dont care about the LINK_CLIENT_ID and LINK_REASON you may want to get just distinct CLIENT_IDs and not include the second two parameters in you select statement...
or
add additional filtering criteria to eliminate unwanted LINK_CLIENT_ID and LINK_REASON combinations
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 05/10/2013 :  17:53:18  Show Profile  Reply with Quote
For the cases where you are getting repeating CLIENT_IDs, which values for LINK_CLIENT_ID and LINK_REASON would you want? One tact to take would be to impose an order on the data using ROW_NUMBER() and then take only the first row in each CLIENT_ID grouping.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
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.05 seconds. Powered By: Snitz Forums 2000