2

I have a assignment table containing employees with employee id and project id, with primary key as assignment id. A project can have multiple employees, and an employee can be in multiple projects. Given an employee id, it's required to get a resultset containing assignment ids of all the projects related to the employee, and all the employees of those projects in a recursive manner. How can this be achieved in sql(preferred) or plsql? I am using Oracle SQL.

Table

assignment_id employee_id project_id
1000 2000 3000
1001 2001 3000
1002 2000 3001
1003 2002 3001
1004 2002 3002
1005 2003 3002
1007 2004 3002
1008 2005 3002
1009 2009 3004

Eg: Given 2000, the below resultset would be returned

assignment_id employee_id project_id
1000 2000 3000
1001 2001 3000
1002 2000 3001
1003 2002 3001
1004 2002 3002
1005 2003 3002
1007 2004 3002
1008 2005 3002

Below query is running into cycles causing huge resultsets for complex hierarchies. Similar issues using connect by.

with all_subs(assignment_id, employee_id, project_id) as 
(
    select assignment_id, employee_id, project_id
    from csm_assignments
    and employee_id = 70001
    union all
    select s.assignment_id, s.employee_id, s.project_id
    from csm_assignments s, all_subs s1
    and (
        (s.employee_id = s1.employee_id and s.project_id != s1.project_id) OR
        (s.employee_id != s1.employee_id and s.project_id = s1.project_id)
    )
) cycle assignment_id set is_loop to 'Y' default 'N'
select * 
from csm_assignments
where assignment_id in (select assignment_id from all_subs);
Crushinator
  • 21
  • 2
  • did up read up about CONNECT BY? You need to show some effort here. – OldProgrammer Jul 05 '22 at 12:31
  • a simple recursive CTE should work just fine – nbk Jul 05 '22 at 12:34
  • Using connect by, or recursive CTE is going into cycles in some use cases resulting a huge resultset or a timeout, for the clause where we need to get all the employees for a project. prior employee_id = employee_id and prior project_id != project_id is working fine to get all projects for an employee, but the other way round is running into loops – Crushinator Jul 05 '22 at 13:02
  • Please provide create table statements, sample data and expected results – Pugzly Jul 05 '22 at 13:24

1 Answers1

0

assignment ids of all the projects related to the employee:

WITH emp_proj as (
    SELECT project_id
    FROM csm_assignments
    WHERE employee_id = 70001
)
SELECT ca.assignment_id
FROM csm_assignments ca
INNER JOIN emp_proj ep on ca.project_id = ep.project_id

all the employees of those projects:

WITH emp_proj as (
    SELECT project_id
    FROM csm_assignments
    WHERE employee_id = 70001
)
SELECT DISTINCT ca.employee_id
FROM csm_assignments ca
INNER JOIN emp_proj ep on ca.project_id = ep.project_id
NickW
  • 8,430
  • 2
  • 6
  • 19