Date:

Share:

create a MySQL hierarchical recursive query

Related Articles

I have a MySQL table as follows:

I want to have a single MySQL query to which I provide the ID as 1 so I need to get all its child IDs [4,5,6,7]

MySQL recursive query without CTE

select  id,
        name,
        parent
from    (select * from mytable
         order by parent, id) mytable,
        (select @pv := '1') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', id)

MySQL SELECT recursive accept all parents with multiple levels

with recursive cte (id, name, parent) as (
  select     id,
             name,
             parent
  from       mytable
  where      parent = 1
  union all
  select     p.id,
             p.name,
             p.parent
  from       mytable p
  inner join cte
          on p.parent = cte.id
)
select * from cte;

outcome

2parent

Answer 2 – Repeat self-connection

create table products
(
    id int,
    name varchar(100),
    parent_id int
);
insert into products
values
    (15, 'category15', 0),
    -- not a descendent of 19
    (16, 'category16', 15),
    -- not a descendent of 19
    (19, 'category19', 0),
    (20, 'category20', 19),
    -- level 1
    (21, 'category21', 20),
    -- level 2
    (22, 'category22', 21),
    -- level 3
    (23, 'category23', 19),
    -- level 1
    (24, 'category24', 21),
    -- level 3
    (25, 'category25', 22),
    -- level 4
    (26, 'category26', 22),
    -- level 4
    (27, 'category26', 25); -- level 5
mysql2
select p6.parent_id as parent6_id,
    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       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id)
order       by 1, 2, 3, 4, 5, 6, 7;
mysqlparent child

http://sqlfiddle.com/#!9/5de2a/46

Hierarchical data in MySQL

CREATE TABLE category
(
    category_id INT
    AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR
    (20) NOT NULL,
        parent INT DEFAULT NULL
);

    INSERT INTO category
    VALUES(1, 'ELECTRONICS', NULL),
        (2, 'TELEVISIONS', 1),
        (3, 'TUBE', 2),
        (4, 'LCD', 2),
        (5, 'PLASMA', 2),
        (6, 'PORTABLE ELECTRONICS', 1),
        (7, 'MP3 PLAYERS', 6),
        (8, 'FLASH', 7),
        (9, 'CD PLAYERS', 6),
        (10, '2 WAY RADIOS', 6);
table looks like
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name="ELECTRONICS";

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

Finding all the nodes of the leaves

Create the leaf nodes in our tree (those without children) by using LEFT JOIN

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Tell us if you liked the post. Only then can we get better.

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Popular Articles