PostgreSQL merge recursive query and JOIN

Question

I have the following schema:

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);

For the sake of simplicity I declared:

1 as employee_id

2, 3, 4 as team_id

5 -> 6 -> 7 as service (5 is the main service)

8 -> 9 (8 is the main service)

10 (10 is the main service)

To retrieve the services the employee belongs to I query

SELECT ls.service_id FROM tbl_team_list_serv ls 
JOIN tbl_employee_team t ON ls.team_id=t.teams_id WHERE t.employee_id = 1

To get the main service from the services I use

WITH RECURSIVE r AS 
(
   SELECT id, parent, 1 AS level
   FROM tbl_service
   WHERE id = 7 /*(here's I need to assign to every id from the JOIN)*/
  
   UNION

   SELECT tbl_service.id, tbl_service.parent, r.level + 1 AS level
   FROM tbl_service
      JOIN r
          ON r.parent = tbl_service.id
)


SELECT id FROM r WHERE r.level = (SELECT max(level) FROM r)

My question is how do I merge the two queries?

Based on the data above I want to finally get a list of ids which is in this case:

5, 8, 10

Also, I want my recursive query to return the last row (I don't think that the solution with level is elegant)

SQLFiddle can be found here

Thanks in advance

Answer 1

I feel like you already did most of the work for this question. This is just a matter of the following tweaks:

• Putting the logic for the first query in the anchor part of the CTE.
• Adding the original service id as a column to remember the hierarchy.
• Tweaking the final logic to get one row per original service.

As a query:

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;

Here is a db<>fiddle.