I have a comments table, which can be simplified down to this:


where parent_id is nullable, but might be a key for its parent comment.

Now, how can I select all descendants of a specific comment?
The comments might be several levels down...

4 Answers 4


Hierarchical queries, as those recursive queries are known, are now supported in MySQL 8.

Old Answer

Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries

You can also find a discussion on how to store hierarchical data with other models than with an Adjacency List (i.e. the Parent column) here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/

Good luck!

  • I wonder how that solution in your second link can be dangerous. Could you explain it? Otherwise, welcome to the site!
    – dezso
    Jul 11, 2013 at 13:12
  • 3
    @dezso: Quoth the crafter of the query himself, Quassnoi "*It's not upgrade-safe* because MySQL does not clearly define session variable behaviour. However, it's the only way to deal with adjacency lists in a timely fashion in-query." Dangerous might have been too strong a word (potentially unstable?) but I prefer to err on the side of caution (plus, I'm more knowledgeable in Oracle than MySQL, so I wanted to be extra precautious). Thanks for the welcome, by the way! I have been a long-time lurker to the SE network, and I decided it was time to pay back a bit.
    – Valmoer
    Jul 11, 2013 at 13:35
  • 3
    The WITH [RECURSIVE] syntax is now supported from mysql 8.0. dev.mysql.com/doc/refman/8.0/en/with.html Dec 17, 2018 at 18:41

This table design is a SQL antipattern "Naive trees" as described by Bill Karwin (staring from slide 48 in his SQL Antipatterns Strike Back presentation). The problem with this design specifically is the difficulty with getting all descendants (or parents) of a node. Since you are using MySQL you can't use common table expressions (the WITH statement and it's RECURSIVE modifier) present in other RDBMSes.

What you're left with is:

  • use an alternate implementation of hierarchical data structure (answers to this question might be a good reference on this)
  • build self join queries with a depth limit. For depth = 5 you could use something in the lines of:

    SELECT *
    FROM comments AS c1
      JOIN comments AS c2 ON (c2.parent_id = c1.id)
      JOIN comments AS c3 ON (c3.parent_id = c2.id)
      JOIN comments AS c4 ON (c4.parent_id = c3.id)
      JOIN comments AS c5 ON (c5.parent_id = c4.id)
  • use a RDBMS which supports WITH RECURSIVE (although this most likely is not an option for most people)

  • 2
    I disagree with Bill Karwin here. The adjacency model is not an anti-pattern. With a modern DBMS that supports recursive queries (Oracle has supported this for over 20 years) such a model is very efficient to retrieve and update. Jul 11, 2013 at 14:20

MySQL does not support recursive queries such as the one you need.

What I did a while back was write Stored Procedures that provide the model for doing so.

Rather than reinvent the wheel, I will give you the links to my past posts on this:

In short, the Stored Procedures I made do preorder tree traversal using queue processing

  • GetParentIDByID
  • GetAncestry
  • GetFamilyTree

Parent to All Children (like GetFamilyTree Stored Procedure)

  • STEP01) Start with a parent_id in a queue
  • STEP02) Dequeue the next parent_id as the current
  • STEP03) Enqueue all the id values that have the current parent_id
  • STEP04) Print or Collect the Comment
  • STEP05) If the queue is not empty, goto STEP02
  • STEP06) You are done !!!

Child to all Parent (like GetAncestry Stored Procedure)

  • STEP01) Start with an id in a queue
  • STEP02) Dequeue the next id as the current
  • STEP03) Enqueue the parent_id value of the current id
  • STEP04) Print or Collect the Comment
  • STEP05) If the queue is not empty, goto STEP02
  • STEP06) You are done !!!

Please look over the Stored Procedures in my other posts to see the implementation.

Give it a Try !!!

SELECT  group_concat(@id :=
        SELECT  id
        FROM    comments
        WHERE   parent_id = @id
        )) AS comment
FROM    (
        SELECT  @id := 1
        ) vars



Not the answer you're looking for? Browse other questions tagged or ask your own question.