SQL With Recursive query – Trying to output a comment tree

  backend, database, mariadb, php, sql

I’m quite stuck with recursive queries in SQL.
I am trying to output a comment tree (like reddit comments)
Eg.
FirstComment On Thread Post: I Like this picture of the cat. I’m Dave

-"Response to FirstComment: Thanks. I’m Sarah"

–Response to 2nd Comment: Good to meet you Sarah"

"Another top level comment: Great cat!"

Response to top level comment: She is a great cat!

Hope this makes sense.

My table is

describe Posts ; 
+---------------+-----------------+------+-----+---------------------+-------------------------------+
| Field         | Type            | Null | Key | Default             | Extra                         |
+---------------+-----------------+------+-----+---------------------+-------------------------------+
| id            | int(6) unsigned | NO   | PRI | NULL                | auto_increment                |
| User          | varchar(30)     | NO   |     | NULL                |                               |
| PostTimeStamp | timestamp       | NO   |     | current_timestamp() | on update current_timestamp() |
| CommentText   | varchar(8000)   | YES  |     | NULL                |                               |
| IDOfThread    | int(11)         | YES  |     | NULL                |                               |
| Upvotes       | int(11)         | NO   |     | 0                   |                               |
| ParentId      | int(11)         | YES  |     | NULL                |                               |
| level         | int(11)         | YES  |     | NULL                |                               |
+---------------+-----------------+------+-----+---------------------+-------------------------------+
8 rows in set (1.142 sec)

Now if I run this sql as a simple select statement

select * from Posts where IDOfThread =  332 ; 
+-----+------+---------------------+------------------------------------------------------+------------+---------+----------+-------+
| id  | User | PostTimeStamp       | CommentText                                          | IDOfThread | Upvotes | ParentId | level |
+-----+------+---------------------+------------------------------------------------------+------------+---------+----------+-------+
| 205 | Twat | 2021-10-14 13:52:14 |   
test top level comment                           |        332 |       0 |     NULL |  NULL |
| 206 | Twat | 2021-10-14 13:52:25 | Response comment                                     |        332 |       0 |      205 |     1 |
| 207 | Twat | 2021-10-14 14:52:57 | :Lenin_Sawnoff 
                                    |        332 |       0 |     NULL |  NULL |
| 208 | Twat | 2021-10-14 15:03:20 | response comment                                     |        332 |       0 |      207 |     1 |
| 209 | Twat | 2021-10-14 15:03:57 | test response to reponse comment (ie.level 2) 
                 |        332 |       0 |      208 |     2 |
| 210 | Twat | 2021-10-14 15:04:15 | reply to leninsawnoff                                |        332 |       0 |      207 |     1 |
| 211 | Twat | 2021-10-14 15:04:48 | response to 205
                                                |        332 |       0 |      205 |     1 |
+-----+------+---------------------+------------------------------------------------------+------------+---------+----------+-------+
7 rows in set (0.103 sec)

I get the 7 comments as expected

However if I run this SQL code (my attempt at recursion)

with recursive cte (id, User, CommentText, IDOfThread, ParentId, level) as (
 select     id,
            User,
            CommentText,
            IDOfThread,
             ParentId,
             level
  from       Posts
  where      IDOfThread = 332
   union all
  select     p.id,
             p.User,
             p.CommentText,
             p.IDOfThread,
            p.ParentId,
           p.level
  from       Posts p
  inner join cte
          on p.ParentId = cte.ParentId 
)
select * from cte

I get the following data with multiple entries

    +------+------+------------------------------------------------------+------------+----------+-------+
| id   | User | CommentText                                          | IDOfThread | ParentId | level |
+------+------+------------------------------------------------------+------------+----------+-------+
|  205 | Twat |   
test top level comment                           |        332 |     NULL |  NULL |
|  206 | Twat | Response comment                                     |        332 |      205 |     1 |
|  207 | Twat | :Lenin_Sawnoff 
                                    |        332 |     NULL |  NULL |
|  208 | Twat | response comment                                     |        332 |      207 |     1 |
|  209 | Twat | test response to reponse comment (ie.level 2) 
                 |        332 |      208 |     2 |
|  210 | Twat | reply to leninsawnoff                                |        332 |      207 |     1 |
|  211 | Twat | response to 205
                                                |        332 |      205 |     1 |
|  206 | Twat | Response comment                                     |        332 |      205 |     1 |
|  208 | Twat | response comment                                     |        332 |      207 |     1 |
|  209 | Twat | test response to reponse comment (ie.level 2) 
                 |        332 |      208 |     2 |
|  210 | Twat | reply to leninsawnoff                                |        332 |      207 |     1 |
|  211 | Twat | response to 205
                                                |        332 |      205 |     1 |
|  209 | Twat | test response to reponse comment (ie.level 2) 
                 |        332 |      208 |     2 |
+------+------+------------------------------------------------------+------------+----------+-------+
13 rows in set (0.018 sec)

Source: Ask PHP

LEAVE A COMMENT