How to fetch data in a tree structure in MySQL? In a single query

Question

I have a table in MySQL where each tuple in the table is a child of a tuple in the same table. For that, I'm inserting data primary Id as partner Id. Refer the following for an example.

Here 'A' parent has two children 'B' and 'C', similarly, 'B' has 'D' and 'C' has 'E' as their children respectively. An example is shown in below image

Now, coming to Question, I want to fetch all children of a given parent. Suppose if I pass G's id 7, it should fetch J, K and L records. If I pass A's id 1 it should fetch all records. How can I achieve this in a SQL query? I have no idea on how to build this MySQL query.

Answer 1

This could be done using a recursive query as follows.

SELECT  id,
        name,
        parent 
FROM    (SELECT * FROM items
         ORDER BY parent, id) items_sorted,
        (SELECT @iv := '5') initialisation
WHERE   find_in_set(parent, @iv)
AND     length(@iv := concat(@iv, ',', id));

Check out this fiddle to see this in action. Also, this is a very similar question that I have referred to, to create this answer.

Next time, please try to provide a Minimal, Complete and Verifiable Example (perhaps an SQL Fiddle for a case like this) so that it is easy for someone who is willing to answer.