title: SQL With Recursive query - Trying to output a comment tree
body: <p>I'm quite stuck with recursive queries in SQL.
  I am trying to output a comment tree (like reddit comments)
  Eg.</p>
  <pre><code>FirstComment On Thread Post: I Like this picture of the cat. I'm Dave
  -Response to FirstComment: Thanks. I'm Sarah&quot;**
  --Response to 2nd Comment: Good to meet you Sarah&quot;**
  Another top level comment: Great cat!&quot;**
  -Response to top level comment: She is a great cat!**
  </code></pre>
  <p>Hope this makes sense.</p>
  <p>My table is</p>
  <pre><code>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)
  </code></pre>
  <p>Now if I run this sql as a simple select statement</p>
  <pre><code>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)
  </code></pre>
  <p>I get the 7 comments as expected</p>
  <p>However if I run this SQL code (my attempt at recursion)</p>
  <pre><code>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
  </code></pre>
  <p>I get the following data with multiple entries</p>
  <pre><code>    +------+------+------------------------------------------------------+------------+----------+-------+
  | 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)
  </code></pre>