MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.36


CREATE TABLE `category_name` (
  `id` bigint NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

INSERT INTO `category_name` (`id`, `name`) VALUES
(1, 'Electronics'),
(2, 'Computers'),
(3, 'Accessories & Supplies'),
(4, 'Camera & Photo'),
(5, 'Computer accessories and peripherals'),
(6, 'Computer components'),
(7, 'Audio & Video Accessories'),
(8, 'Camera & Photo Accessories'),
(9, 'Accessories'),
(10, 'Bags & Cases'),
(11, 'Audio & Video Accessories'),
(12, 'Blank Media'),
(13, 'Computer screws'),
(14, 'Table Barebones'),
(15, '3D Glasses'),
(16, 'Antennas'),
(17, 'Accessory Bundles'),
(18, 'Bags & Cases'),
(19, 'Accessory Kits'),
(20, 'Bags & Cases'),
(21, 'Bag & Case Accessories'),
(22, 'Binocular Cases'),
(23, 'Computer headsets'),
(24, 'Computer microphones'),
(25, 'Discs BD-R'),
(26, 'Discs BD-RE'),
(27, 'bolts'),
(28, 'frame'),
(29, 'lamps'),
(30, 'coasters');

Records: 30  Duplicates: 0  Warnings: 0


CREATE TABLE `tree_path` (
  `children` bigint NOT NULL,
  `parent` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;


INSERT INTO `tree_path` (`children`, `parent`) VALUES
(1, 1),
(3, 1),
(4, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(15, 1),
(16, 1),
(17, 1),
(18, 1),
(19, 1),
(20, 1),
(21, 1),
(22, 1),
(2, 2),
(5, 2),
(6, 2),
(11, 2),
(12, 2),
(13, 2),
(14, 2),
(23, 2),
(24, 2),
(25, 2),
(26, 2),
(27, 2),
(28, 2),
(29, 2),
(30, 2),
(3, 3),
(7, 3),
(8, 3),
(15, 3),
(16, 3),
(17, 3),
(18, 3),
(4, 4),
(9, 4),
(10, 4),
(19, 4),
(20, 4),
(21, 4),
(22, 4),
(5, 5),
(11, 5),
(12, 5),
(23, 5),
(24, 5),
(25, 5),
(26, 5),
(6, 6),
(13, 6),
(14, 6),
(27, 6),
(28, 6),
(29, 6),
(30, 6),
(7, 7),
(15, 7),
(16, 7),
(8, 8),
(17, 8),
(18, 8),
(9, 9),
(19, 9),
(20, 9),
(10, 10),
(21, 10),
(22, 10),
(11, 11),
(23, 11),
(24, 11),
(12, 12),
(25, 12),
(26, 12),
(13, 13),
(27, 13),
(28, 13),
(14, 14),
(29, 14),
(30, 14),
(15, 15),
(16, 16),
(17, 17),
(18, 18),
(19, 19),
(20, 20),
(21, 21),
(22, 22),
(23, 23),
(24, 24),
(25, 25),
(26, 26),
(27, 27),
(28, 28),
(29, 29),
(30, 30);

Records: 98  Duplicates: 0  Warnings: 0


ALTER TABLE `category_name`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tree_path`
  ADD PRIMARY KEY (`children`,`parent`),
  ADD KEY `FK_PARENT` (`parent`);

ALTER TABLE `category_name`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

ALTER TABLE `tree_path`
  ADD CONSTRAINT `FK_CHILDREN` FOREIGN KEY (`children`) REFERENCES `category_name` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_PARENT` FOREIGN KEY (`parent`) REFERENCES `category_name` (`id`) ON DELETE CASCADE;
COMMIT;

Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 30  Duplicates: 0  Warnings: 0



Records: 98  Duplicates: 0  Warnings: 0


SELECT p.id, p.name, count(*) - l.level AS levelx
FROM   tree_path t, 
       ( SELECT count(*) -1 AS level 
         FROM tree_path 
         WHERE children = 3
       ) l, 
       (  SELECT * 
          FROM category_name f 
               INNER JOIN tree_path t 
                    ON f.id = t.children 
           WHERE t.parent = 3 
           AND t.children!=t.parent
        ) p 
WHERE p.children = t.children 
GROUP BY p.id, p.name, l.level
HAVING levelx = 2
;


id
name
levelx


7
Audio & Video Accessories
2

8
Camera & Photo Accessories
2