Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stahorse
Yak Posting Veteran
86 Posts |
 Posted - 2013-05-09 : 04:55:46
|
| HiI 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_HSTSelect LINK_CLIENT_ID,CLIENT_ID,LINK_REASONFROM #PR_LINK_INV_HSTdeclare @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 ctedrop table #PR_LINK_INV_HST |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-09 : 11:36:54
|
| 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 |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-05-10 : 17:53:18
|
| 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 |
|
|
|
|
|
|
|
|
CTE Recursive