MySQL: Select All Parent Nodes Of A Child Without Recursion

Selecting all parent nodes of a child node in a hierarchical data structure can be a common task in database management. In this article, we will explore How to Select All Parent Nodes of a Child without using Recursion in MySQL.

The basic idea is to use a self-join to select all the parent nodes of a child node. A self-join is a join of a table to itself, and it is used to combine rows from the same table based on a related column. In this case, we will use the parent_id column to join the table to itself and retrieve all parent nodes of a given child node.

Table of Contents

Select All Parent Nodes of a Child Node

Let’s start creating the query to select all parent nodes of a child id 4.

SELECT T2.id, T2.name, T2.parent_id parentId

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM myTable WHERE id = _id) AS parent_id

FROM

(SELECT @r := 4) vars,

myTable m

) T1

JOIN myTable T2 ON T1._id = T2.id

ORDER BY T2.id ASC

The above code joins the same table to run through all the parents until the parent_id becomes null. It gives a flat result in row-column format. Later you can convert the result to JSON object using some programming language.

Replace myTable with your actual table name and provide the parent id in @r variable and run the query.

The output of above query

I have created a sample table with some sample values to show how it works.

create table myTable(

id int,

name varchar(255),

parent_id int

);

insert into myTable values(1, 'Tree1', NULL);

insert into myTable values(2, 'SubTree1', 1);

insert into myTable values(3, 'SubTree2', 1);

insert into myTable values(4, 'SubTree12', 2);

insert into myTable values(5, 'SubTree13', 3);

Simple select * from myTable result

The simple select of the above data gives following result-

Select all parent nodes of a child node 4 without recursion

Following result is all parent nodes of a child node where id I have provided is 4.

Select all parent nodes of a child node without recursion

The above result gives you all the parents of provided id 4. If you look into the inserted data, id 2 and 1 are the parents of id 4.

Here is the SQLFiddle link that will give the above output.

Conclusion

Selecting all parent nodes of a child node in a hierarchical data structure is a common task in database management. MySQL, being a popular and widely used relational database management system, offers several ways to achieve this task. One common approach was to use recursion, which can be implemented using a technique called a “recursive common table expression” (CTE). However, this approach would be performance intensive, especially when working with large or deep trees.

This article described how can you get all the parent nodes of a child without using the recursion technique. The query works well in MySQL Server lower than 8 also.

Select custom formatted date in MS SQL with inbuilt FORMAT syntax.