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.
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
@r AS _id,
(SELECT @r := parent_id FROM myTable WHERE id = _id) AS parent_id
(SELECT @r := 4) vars,
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.
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(
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.
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.
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