Complex SQL Query Self Referential table

Question

I have a self referential table:

ID|NAME|PARENT_ID
1 |P   |NULL
2 |C1  | 1
3 |C2  | 1
4 |C3  | 2
5 |C4  | 4

I'm trying to write a query to get all the children of an ID (infinite levels) For example: When input is 1, i want all the rows which are descendants of 1 i.e

ID|NAME|PARENT_ID
1 |P   |NULL
2 |C1  | 1
3 |C2  | 1
4 |C3  | 2
5 |C4  | 4

When input is 2:

ID|NAME|PARENT_ID
4 |C3  | 2

Please check this SQL Fiddle.

I have reached here so far:

select id as productid,name,@pv:=parent_id 
from products 
join (select @pv:=1)tmp 
where parent_id=@pv

But it only gives me two level records, I need infinite levels of records.

Thank you, Sash

This is a hierarchical query. MySQL basically has no support for hierarchical and recursive queries. Either you need to change your data structure or use recursive stored procedures. A simple `select` statement cannot do what you want. — Gordon Linoff, Nov 15 '15 at 15:25
similar question http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159 — Suing, Nov 15 '15 at 16:25
Further to Gordon's comment, this is best I've used so far https://en.wikipedia.org/wiki/Nested_set_model (it looks chaotic at first glance but it's actually extremely simple to use) — rjdown, Nov 15 '15 at 16:25
Check this: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Axalix, Nov 15 '15 at 16:27

trincot · Accepted Answer · 2015-11-18T09:09:08.307

You can achieve this with the following query:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) base,
        (select @pv := '1') tmp
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

Here is a fiddle based on the one provided in the question.

The value specified in @pv := '1' should be set to the id of the parent you want to select all the descendants of.

This will work also if a parent has multiple children. However, it is required that for each record parent_id < id, otherwise the results will not be complete.

Also note that for very large data sets this solution might get slow, as the find_in_set operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.

NB: If you want to have the parent node itself also included in the result set, then prefix the following before the above SQL with the id value of interest in the where clause:

select  id,
        name,
        parent_id 
from    products
where   id = '1'
union
...

Alternative 1: CONNECT BY

Some other databases have a specific syntax for hierarchical look-ups, such as the CONNECT BY clause available on Oracle databases. MySql does not offer such a syntax.

Alternative 2: smarter identifiers

Things become a lot easier if you would assign id values that contain the hierarchical information. For example, in your case this could look like this:

ID      | NAME
1       | P   
1-1     | C1  
1-2     | C2  
1-1-1   | C3  
1-1-1-1 | C4

Then your select would look like this:

select  id,
        name 
from    products
where   id like '1-%'

Alternative 3: Repeated Self-joins

If you know an upper limit for how deep your hierarchy tree can become, you can use a standard sql like this:

select      p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       1 in (p1.parent_id, 
                  p2.parent_id, 
                  p3.parent_id, 
                  p4.parent_id, 
                  p5.parent_id) 
order       by 1, 2, 3, 4, 5, 6;

See this fiddle

The where condition specifies which parent you want to retrieve the descendants of. You can extend this query with more levels as needed.

Hi, i'm using the first query, how can i get the parent category too including the childs? — Pruthvi Raj Nadimpalli, Nov 18 '15 at 05:05
Can you clarify what you mean? Do you mean that you want the value that you have specified in `@pv := '1'` to be included in a separate column, with a value that will be the same for all records (`1` in this example)? — trincot, Nov 18 '15 at 08:29
No. actually i want the record that you i have specified in @pv := '1' as a row along with child records — Pruthvi Raj Nadimpalli, Nov 18 '15 at 08:52
I added an extension of the query for that purpose in my answer in a new paragraph "NB: ...". It is quite straightforward, just `union` that one record to the result set. By pre-pending it to the base `SQL` you should also get it as the first record in your result set. — trincot, Nov 18 '15 at 09:12

Complex SQL Query Self Referential table

1 Answers1