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

stahorse
Yak Posting Veteran

85 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

547 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
1754 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  
 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.14 seconds. Powered By: Snitz Forums 2000