Devart Forums

Code: Select all

-- List all childs (v3 - function)
with rCTE AS
(
   -- Start node
   SELECT r2.FullName AS Output,r1.$node_id AS parentid, r2.$node_id as bottomnode,1 as Tree
   FROM Animal r1 
   JOIN ChildGenealogyLink e ON e.$from_id = r1.$node_id 
   JOIN Animal r2 ON r2.$node_id = e.$to_id 
	AND r1.FullName IN ('F''Sheree Jones Of Dawson City')
	--AND r1.BreederId = 1
   UNION ALL
   -- Recurse part
   SELECT r.FullName,c.bottomnode,r.$node_id,Tree + 1
   FROM rCTE c
   JOIN ChildGenealogyLink e ON e.$from_id = c.bottomnode
   JOIN Animal r ON r.$node_id = e.$to_id
)
SELECT output AS 'Full name' FROM rCTE
OPTION (MAXRECURSION 1000);		-- Set max recursion level