Iterate a join in SQL Server

Question

I am trying to iterate through one specific table. All the table has is a Parent ID and a Child ID. What I want is to select all children and then use those as a parent to select the next set of children. It is possible for a parent ID to be linked to multiple Children. all I know beforehand is the first parent ID.

This is what I tried:

DECLARE @Child AS int

SET @Child = 117019

WHILE @Child IS NOT NULL
BEGIN

    SELECT
        @Child = adsa.FK_CHILDASSEMBLY
    FROM
        ASSEMBLYDETAILSUBASSEMBLY adsa
    WHERE
        adsa.FK_PARENTASSEMBLY = @Child

    PRINT @Child
END;

This just results in an infinite loop of the 1st child id but what I want is a full list of all the unique child ID's that are in some way linked to each other.

Answer 1

This is a pretty simple recursive CTE:

It's not clear what the foreign key is against, I have used CHILDASSEMBLY_ID

DECLARE @parent AS int = 117019;

WITH cte AS (
    SELECT
        @parent CHILDASSEMBLY_ID
    UNION ALL
    SELECT
        adsa.CHILDASSEMBLY_ID
    FROM
        ASSEMBLYDETAILSUBASSEMBLY adsa
    JOIN
        cte ON cte.FK_CHILDASSEMBLY = adsa.CHILDASSEMBLY_ID
)
SELECT *
FROM cte