sql - How to create a MySQL hierarchical recursive query -

i have mysql table follows:

id | name        | parent_id 19 | category1   | 0 20 | category2   | 19 21 | category3   | 20 22 | category4   | 21 ...... 

now, want have single mysql query supply id [for instance 'id = 19'] should child ids [i.e. result should have ids '20,21,22'].... also, hierarchy of children not known can vary....

also, have solution using loop..... let me know how achieve same using single mysql query if possible.

for mysql versions not support common table expressions (up version 5.7), achieve following query:

select  id,         name,         parent_id     (select * products          order parent_id, id) products_sorted,         (select @pv := '19') initialisation   find_in_set(parent_id, @pv) > 0 ,     @pv := concat(@pv, ',', id) 

here fiddle.

the value specified in @pv := '19' should set id of parent want select descendants of.

this work if parent has multiple children. however, required each record fulfills condition parent_id < id, otherwise results not complete.

this query uses specific mysql syntax: variables assigned , modified during execution. assumptions made order of execution:

• the from clause evaluated first. @pv gets initialised.
• the where clause evaluated each record in order of retrieval from aliases. condition put include records parent identified being in descendant tree (all descendants of primary parent progressively added @pv).
• the conditions in where clause evaluated in order, , evaluation interrupted once total outcome certain. therefore second condition must in second place, adds id parent list, , should happen if id passes first condition.

all in all, 1 may find these assumptions risky rely on -- there no documented guarantee them, , though works consistently, evaluation order may in theory still change when use query view or sub-query in larger query.

also note large data sets solution might slow, find_in_set operation not ideal way find number in list, not in list reaches size in same order of magnitude number of records returned.

alternative 1: with recursive, connect by

more , more databases implement sql:1999 iso standard with [recursive] syntax recursive queries (e.g. postgres 8.4+, sql server 2005+, db2, oracle 11gr2+, sqlite 3.8.4+, firebird 2.1+, h2, hypersql 2.1.0+, teradata, mariadb 10.2.2+). , of version 8.0, mysql supports it. syntax query looks this:

with recursive cte (id, name, parent_id) (  select     id,             name,             parent_id        products       parent_id = 19  union  select     p.id,             p.name,             p.parent_id        products p  inner join cte          on p.parent_id = cte.id ) select * cte; 

some databases have alternative, non-standard syntax hierarchical look-ups, such connect by clause available on oracle databases. db2 supports alternative syntax.

mysql version 5.7 not offer such feature. when database engine provides syntax, best option go for. if not, consider following alternatives.

alternative 2: path-style identifiers

things become lot easier if assign id values contain hierarchical information: path. example, in case this:

id       | name 19       | category1    19/1     | category2   19/1/1   | category3   19/1/1/1 | category4   

then select this:

select  id,         name     products   id '19/%' 

alternative 3: repeated self-joins

if know upper limit how deep hierarchy tree can become, can use standard sql this:

select      p6.parent_id parent6_id,             p5.parent_id parent5_id,             p4.parent_id parent4_id,             p3.parent_id parent3_id,             p2.parent_id parent2_id,             p1.parent_id parent_id,             p1.id product_id,             p1.name        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       19 in (p1.parent_id,                     p2.parent_id,                     p3.parent_id,                     p4.parent_id,                     p5.parent_id,                     p6.parent_id)  order       1, 2, 3, 4, 5, 6, 7; 

see fiddle

the where condition specifies parent want retrieve descendants of. can extend query more levels needed.