1

I have a table to store binary tree data in the following structure

enter image description here

Lets say for userId 27, currently I'm fetching a bunch of rows and building the tree in code.
Is there an efficient way to fetch the all the rows which belonging to the tree for userId 27 (as nodes) via sql query?
I have MySQL 8.0.21.

Som
  • 91
  • 1
  • 10
  • What do you mean: "*as nodes*"? SQL is used to fetch, modify data, but not for data graphical presentation. What have you tried so far? What's expected output? – Maciej Los Sep 26 '20 at 07:33

1 Answers1

2

In MySQL 8.0, one option uses a recursive query:

with recursive cte as (
    select t.* from mytable where userid = 27
    union all
    select t.*
    from cte c
    inner join mytable t on t.parentid = c.userid
)
select * from cte
GMB
  • 216,147
  • 25
  • 84
  • 135