Exiting a recursive expression on a shared table when the result set contains some value

Question

Given the following table:

create table TreeNode ( ID int not null primary key, ParentID int null foreign key references TreeNode (ID) ) 

How can I write a generic table expression to start in the root directory (WHERE ParentID IS NULL) and traverse its descendants until the result set contains some target node (for example, WHERE ID = n)? It is easy to start from the target node and move up to the root, but this will not lead to the creation of the same result set. In particular, nodes that have the same parent as the target node will not be included.

My first attempt:

 with Tree as ( select ID, ParentID from TreeNode where ParentID is null union all select a.ID, a.ParentID from TreeNode a inner join Tree b on b.ID = a.ParentID where not exists (select * from Tree where ID = @TargetID) ) 

Which gives the error: Recursive member of a common table expression 'Tree' has multiple recursive references.

NOTE I'm only interested in a top-down walk.

Answer 1

UPDATE 2:

The third attempt to "cross" the tree in both directions.

Create the CTE of all ParentIDs from Target to root . Then select from tree nodes whose ID or Parent displayed in a short list.

 -- ; WITH Tree AS ( SELECT ID ,ParentID FROM TreeNode WHERE [ID] = @targetId UNION ALL SELECT a.ID ,a.ParentID FROM TreeNode a INNER JOIN Tree b ON b.ParentID = a.ID ) SELECT * FROM [dbo].[TreeNode] n WHERE EXISTS (SELECT * FROM [Tree] t WHERE [t].[ID] = [n].[ID] OR [t].[ID] = [n].[ParentID] )