SQL Optimization, Nested Query on the same table-CodePudding

i have to create a query that return employees having mutliple territories parent for the same function code :

Table employee_territory_function

employee_id| employee_function_id  | territory_id
 ---------- ----------------------- ------------- 
| 12345    | C1                    |  t1        |
| 12345    | C1                    |  t2        |
| 12346    | C2                    |  t3        |
| 12346    | C2                    |  t4        |
| 12347    | C4                    |  t8        |

Table territory

territory_id| territory_parent_id 
 ----------- ------------------- 
| t1        | P1                |
| t2        | P1                |
| t3        | P2                |
| t4        | P3                |
| t8        | P8                |

the result must be the employee_id 12346 which have multiple parents

my query was :

select * from employee_territory_function tr1 where tr1.employee_id in (
select  ee.employee_id  from (
select et.employee_id from employee_territory_function et 
join territory territory on territory.id = et.territory_id
where et.employee_id in ( 
select etf.employee_id ,etf.employee_function_id from employee_territory_function etf
group by etf.employee_id ,etf.employee_function_id having count(*)>1)) ee 
group by ee.employee_id ,ee.employee_function_id ,ee.territory_parent_id having count(*)  =1)

The query takes much time execution with 10k for the couple ( employee , function code ) is there a way to optimize or rewrite the query differently ?

CodePudding user response：

SELECT E.EMPLOYEE_ID,E.EMPLOYEE_FUNCTION_ID
 FROM EMPLOYEE AS E
 JOIN TERRITORY AS T ON E.TERRITORY_ID=T.TERRITORY_ID
 GROUP BY E.EMPLOYEE_ID,E.EMPLOYEE_FUNCTION_ID
 HAVING MIN(T.TERRITORY_PARENT_ID)<>MAX(T.TERRITORY_PARENT_ID)

Based on your sample data