MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);


Records: 9  Duplicates: 0  Warnings: 0


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


id
name
father


1
dp1
null

4
dp4
1

6
dp6
4

7
dp7
6

8
dp8
6

9
dp9
6