SQL Optimization, Nested Query on the same table

Question

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 ?

Sergey · Accepted Answer · 2022-04-19 09:00:10Z

1

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

answered Apr 19, 2022 at 9:00

Sergey

5,2801 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mirlo Over a year ago

best response even seen, thanks @Sergey

Collectives™ on Stack Overflow

SQL Optimization, Nested Query on the same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related