I have a table that I need to pull all the child client IDs when I enter the parent id. The code would keep running through till the clientid = the parentid. in the table below if I entered parentid 01 the results would be clientid 01, 02, and 04.

if a loop is not the best way to do this, what is the best option?

DECLARE @Client varchar(20)
set @Client='1000'
WITH _RecursiveCTE AS
(
-- Base case
SELECT iClientID, iParentClientID, sClientCode
FROM tblClient
WHERE iClientID <> iParentClientID -- Prevents infinite recursion with 
your specific data model

UNION ALL

-- Recursive case
SELECT C.iClientID, C.iParentClientID, C.sClientCode
FROM _RecursiveCTE AS RC -- Notice the self-referenced CTE here creates 
the recursion
INNER JOIN tblClient AS C ON RC.iClientID = C.iParentClientID
)

SELECT iClientID, iParentClientID, sClientCode
FROM _RecursiveCTE
WHERE sClientCode = @Client

UNION ALL

-- Makes sure we don't miss out on the case we pre-filtered from the 
recursive CTE
SELECT iClientID, iParentClientID, sClientCode
FROM tblClient
WHERE iClientID = iParentClientID
AND sClientCode = @Client

CodePudding user response：

As mentioned in the comments, Recursive CTEs are generally the way to go when solving tree-type / hierarchy problems. Loops are generally not a great tool in relational databases which are meant to solve problems relationally, with set-based solutions.

Example recursive CTE:

WITH _RecursiveCTE AS
(
    -- Base case
    SELECT ClientID, ParentID, ParentID AS OriginalParentID
    FROM dbo.YourTable
    
    UNION ALL

    -- Recursive case
    SELECT YC.ClientID, YC.ParentID, RC.OriginalParentID
    FROM _RecursiveCTE AS RC -- Notice the self-referenced CTE here creates the recursion
    INNER JOIN dbo.YourTable AS YC
        ON RC.ClientID = YC.ParentID
    WHERE RC.ClientID <> RC.ParentID -- Prevents infinite recursion with your data model
)

SELECT ClientID
FROM _RecursiveCTE
WHERE OriginalParentID = '01';