Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl_employee_team
(
  employee_id int,
  teams_id int
);

INSERT INTO tbl_employee_team 
VALUES 
(1, 2),
(1, 3),
(1, 4);

CREATE TABLE tbl_team_list_serv
(
  service_id int,
  team_id int
);

INSERT INTO tbl_team_list_serv
VALUES
(7, 2),
(9, 3),
(10, 4);

CREATE TABLE tbl_service
(
  id int,
  parent int
);

INSERT INTO tbl_service
VALUES
(5, null),
(6, 5),
(7, 6),

(8, null),
(9, 8),

(10, null);

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 6


SELECT ls.service_id, s.parent, 1 as level
FROM tbl_team_list_serv ls JOIN
     tbl_employee_team t
     ON ls.team_id = t.teams_id JOIN
     tbl_service s 
     ON ls.service_id = s.id
WHERE t.employee_id = 1


service_id
parent
level


7
6
1

9
8
1

10
null
1



SELECT 3


WITH RECURSIVE r AS  (
     SELECT ls.service_id as id, s.parent, 1 as level, ls.service_id as orig_service_id
     FROM tbl_team_list_serv ls JOIN
          tbl_employee_team t
          ON ls.team_id = t.teams_id JOIN
          tbl_service s 
          ON ls.service_id = s.id
     WHERE t.employee_id = 1
     UNION ALL
     SELECT s.id, s.parent, r.level + 1 AS level, r.orig_service_id
     FROM tbl_service s JOIN
          r
          ON r.parent = s.id
   )
SELECT r.id
FROM (SELECT r.*,
             MAX(level) OVER (PARTITION BY orig_service_id) as max_level
      FROM r
     ) r
WHERE r.level = max_level


id


5

8

10



SELECT 3