Recursion in SQL Server using CTE not working

Question

I am using CTE for the first time and I am trying to use recursion to get the parent tree, but I am not able to get the results correctly.

I have already looked for help here and tried different approaches to stop the recursion but all failed.

I am using SQL Server to run it.

This is the definition of my table for testing:

CREATE TABLE recursiveQuery
(
    id numeric(13),
    name varchar(50),
    parent numeric(13),
    parentName varchar(50)
)

This is the data to query from:

 insert into recursiveQuery values(1, 'QQQ', NULL, NULL)
 insert into recursiveQuery values(44, 'MMM', 1, 'QQQ') 
 insert into recursiveQuery values(33, 'AAA', 44, 'MMM')
 insert into recursiveQuery values(22, 'GGG', 33, 'AAA')
 insert into recursiveQuery values(55, 'JJJ', 33, 'AAA')
 insert into recursiveQuery values(66, 'PPP', 1, 'QQQ') 

And this is the query I wrote so far, but I only get the first line of the tree.

All child nodes are missing and I really don't know what I am doing wrong.

I wanted the result to have the complete top-down parent-child path as varchar like: 'QQQ -> MMM -> AAA -> GGG' (but for all records). I don't know if that will take a long time to run.

If that is not possible, I wanted the query to result in a simple way to identify parent-child paths.

WITH parent_tree AS 
(
    SELECT
        id, name, parent, parentName, 1 AS level
    FROM 
        recursiveQuery m
    WHERE 
        parent IS NULL

    UNION ALL
 
    SELECT 
        rr.id, rr.name, rr.parent, rr.parentName, level + 1 AS level
    FROM 
        recursiveQuery rr
    INNER JOIN 
        parent_tree r ON r.parent = rr.id 
)
SELECT *
FROM parent_tree 
ORDER BY level
OPTION(MAXRECURSION 0)

Answer 1

@DaleK nailed it in the comments - your join criteria are backwards. But I come here to show you the way that I write these sorts of queries to make that easy(-ier) to spot. Specifically, alias the tables/cte in the recursive part of the cte as parent and child. Like so:

WITH parent_tree AS (
  SELECT
    id, name, parent, parentName, 1 as level

  FROM #recursiveQuery m
  WHERE parent is null 

  UNION ALL

  SELECT 
    child.id, child.name, child.parent, child.parentName, level + 1 as level

  FROM #recursiveQuery as child
  inner JOIN parent_tree as parent
    --ON parent.parent = child.id
    on child.parent = parent.id
)

SELECT *
FROM parent_tree 
order by level

I left in what you had originally as a commented out line. Notice how it feels weird to match on the parent's parent? It should, because it's not what you want! Parent/child is only an example - the point is to use the aliases to your advantage to make the relationship obvious.