I made the following tweaks to your CTE to give what I hope is a better result:

1. Fixed the main problem, which was that the anchor member of the "child" CTE had this: WHERE LINK_CLIENT_ID = @CLIENT_ID but it should have had WHERE CLIENT_ID = @CLIENT_ID.

2. I changed the "LEVEL" value of the anchor member from 1 to 0. That way numbers greater than 0 are clearly "child" rows, and numbers less than 0 are "parent" rows. 0 is the client himself or herself.

2. Consolidated the two CTEs into one CTE with two recursive members: the first one finds the "child" clients, and the second recursive member files the "parent" clients. I have had a hard time thinking of a good use of multiple recursive members, but you found one: searching both down and up from any given point in a hierarchy.

3. Added DISTINCT to the insertion into the #Results table, since the results could contain duplicate rows by the nature of the recursive search.

4. Added and ORDER BY to the final SELECT in order to see the hierarchy in sequence from top to bottom.

--create PR_LINK_INV_HST temp table--

Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))

--insert into PR_LINK_INV_HST temp table--

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-14')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-33')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana',null)

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-19')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith',null)

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-16')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-17')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo',null)

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-50','Josh','1-23')

DECLARE @CLIENT_ID VARCHAR(15)

SET @CLIENT_ID = '1-33'

-- This CTE search for the linked clients --

;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])

AS

(

/* Anchor member - the selected client*/

SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0

FROM #PR_LINK_INV_HST

WHERE CLIENT_ID = @CLIENT_ID

UNION ALL

/* Recursive member to search for the child clients. */

SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1

FROM #PR_LINK_INV_HST HST

JOIN pr_linked LNK

ON LNK.LINK_CLIENT_ID = HST.CLIENT_ID

where

lnk.LEVEL >= 0

/* Recursive member to search for the parent clients. */

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 LNK.CLIENT_ID = HST.LINK_CLIENT_ID

where lnk.LEVEL <= 0

)

SELECT distinct *

INTO #RESULTS

FROM pr_linked

-- display result

SELECT *

FROM #RESULTS

order by LEVEL, NAME

drop table #RESULTS

drop table #PR_LINK_INV_HST