MySql Recursive - get all children and parents from a given id

Question

MySQL Version 8.0 Schema SQL

CREATE TABLE IF NOT EXISTS `department` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `father` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_department_department_idx` (`father` ASC) VISIBLE,
  CONSTRAINT `fk_department_department`
    FOREIGN KEY (`father`)
    REFERENCES `department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

insert into department (id,name,father)
values
(1, 'dp1',null),
(2, 'dp2',null),
(3, 'dp3',1),
(4, 'dp4',1),
(5, 'dp5',2),
(6, 'dp6',4),
(7, 'dp7',6),
(8, 'dp8',6),
(9, 'dp9',6);

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';

My query:

WITH RECURSIVE cte_department AS (
      SELECT 
          d1.id, 
          d1.name, 
          d1.father
      FROM
          department d1
      WHERE
          d1.id=6
    UNION ALL
      SELECT 
          d2.id, 
          d2.name, 
          d2.father
      FROM
          department d2
      INNER JOIN cte_department cte ON cte.id = d2.father
)
SELECT * FROM cte_department;

Result:

id  name    father
6   dp6      4
7   dp7      6
8   dp8      6
9   dp9      6

What I need:

id  name    father
1   dp1      null
4   dp4      1
6   dp6      4
7   dp7      6
8   dp8      6
9   dp9      6

The problem is: I can get all childrens, but I need to add to this query all the parents from the given ID, in this case, the ID 6. I'm stuck with that. If someone can help me, follow the fiddle.

https://www.db-fiddle.com/f/g8YkE3hqsvaw8G9vdHPyyF/0

Andrei Odegov · Answer 1 · 2020-12-20T07:25:21.530

4

The recursive part can have multiple query blocks.

WITH RECURSIVE cte_department AS (
      SELECT 
          d1.id,
          d1.name,
          d1.father,
          'Begin' state
      FROM
          department d1
      WHERE
          d1.id=6
    UNION ALL
      SELECT 
          d2.id,
          d2.name,
          d2.father,
          'Up'
      FROM
          department d2
      INNER JOIN
          cte_department cte
      ON
          cte.father = d2.id
      WHERE
          cte.state in ('Begin', 'Up')
    UNION ALL
      SELECT 
          d2.id,
          d2.name,
          d2.father,
          'Down'
      FROM
          department d2
      INNER JOIN
          cte_department cte
      ON
          cte.id = d2.father
      WHERE
          cte.state in ('Begin', 'Down')
)
SELECT
    id, name, father
FROM
    cte_department
ORDER BY
    father, id, name;

Try it on db<>fiddle.

edited Dec 20 '20 at 07:25

answered Dec 20 '20 at 07:19

Andrei Odegov

2,925
2
15
21

This is a great solution. I tried the same in Oracle, but Oracle doesn't allow a second `UNION ALL` in the recursive part, not even when I use parentheses. I had two merge your 'Up' and 'Down' queries into one Up/Down query instead. It's great that MySQL allows for `UNION ALL` here to get the query very readable. Thanks for sharing this. – Thorsten Kettner Dec 20 '20 at 08:57
@ThorstenKettner *It's great that MySQL allows for UNION ALL here* MySQL allows any amount of recursive blocks and mix of UNION ALL / DISTINCT between them (but it does not define the order of recursive subqueries processing, so in some cases the output may be even non-deterministic). – Akina Dec 20 '20 at 09:16
@Akina: I haven't even asked a question. I'm not the OP :-) – Thorsten Kettner Dec 20 '20 at 16:03

score 4 · Accepted Answer · answered Dec 20 '20 at 11:38

I would use two separate recursive queries: one to bring the children, the other for the parents, and then union the results. You can keep track of the level of each node to properly order the records int he resultset:

with recursive 
    children as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select c.lvl, d.* from department d inner join children c on c.id = d.father
    ),
    parents as (
        select 1 as lvl, d.* from department d where id = 6
        union all
        select p.lvl - 1, d.* from department d inner join parents p on d.id = p.father
    )
select * from parents
union   -- on purpose, to remove the duplicate on id 6
select * from children
order by lvl;

This is safer than having multiple union all members in the same query. MySQL does not guarantee the order of evaluation of the members in the recursion, so using this technique could lead to unexpected behavior.

Demo on DB Fiddle

Unrelated to your question, but: the following can be seen in your code:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';

Just don't. ONLY_FULL_GROUP_BY is there for a good reason, that is to have MySQL behave consistenly with the SQL standard as regard to aggregation query. Disabling this SQL mode is never a good idea.

Thx for the answer and for tip about `ONLY_FULL_GROUP_BY` – Fábio Dec 24 '20 at 02:54 — Fábio, Dec 24 '20 at 02:54
This the spirit my man - perfect answer +9000 reputation – csstudent1418 Nov 10 '22 at 19:17 — csstudent1418, Nov 10 '22 at 19:17

MySql Recursive - get all children and parents from a given id

2 Answers2