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

Answer 1

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.

Answer 2

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.