复杂的SQL查询自引用表

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

Answer 1

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.