CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-queryconnect-by
Crushinator
Crushinator

Reputation: 21

Multi column hierarchical query in Oracle SQL

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

Upvotes: 2

Views: 218

Answers (1)

NickW
NickW

Reputation: 9798

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

Upvotes: 0

Related Questions