CODEWITHSUNDEEP
mysqlsqltreehierarchical-datahierarchical-query
Som
Som

Reputation: 91

SQL query to obtain entire tree by root id

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.

Upvotes: 1

Views: 469

Answers (1)

GMB
GMB

Reputation: 222402

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

Upvotes: 2

Related Questions