MySQL request for Node tree

Question

I have a node tree in a MySQL Base and I want to get result like this

Node_1
  Node_1_1
    Node_1_1_1
    Node_1_1_2
  Node_1_2
   Node_1_2_1
Node_2

Now I have a query that gives me only one deep child and all rests at the end of a list. ( Node_1_1_2 would be the last in this example )

Here is my code:

SELECT name, 
    if ( parentId = -1, "Root", 
      if ( exists( SELECT id FROM citizensTree AS t2 WHERE t1.id = t2.parentId), "Inner", "Leaf")
    )
  AS type FROM citizensTree as t1

I love this SQL solution:

SELECT t.id, t.name FROM tree t CONNECT BY prioir id = parentId START WITH parentId = -1

But it doesnt work in MySQL

A recursive CTE will walk the tree as you need. Please include the sample data that would produce that tree. — The Impaler, Sep 05 '21 at 00:27

score 0 · Answer 1 · answered Sep 05 '21 at 00:03

The CONNECT BY is non-standard SQL syntax. It's supported by Oracle and Informix as far as I know, but definitely not MySQL.

MySQL 8.0 supports recursive queries using standard ANSI/ISO SQL syntax. See https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples

Earlier versions of MySQL do not support this syntax. You can simulate recursive queries using a variety of workarounds. See my answer to What is the most efficient/elegant way to parse a flat table into a tree?

MySQL request for Node tree

1 Answers1