[SQL] Recursion in SQL Server using CTE not working

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)

Continue reading...