Connect by prior question

[Wholsea]

Okay, Looking at an organizational structure, employees and managers.

I am trying to find all managers who do not have managers that report to them.

The employee table is set up like this:

Employees
----------
employee_id
manager_emp_id
employee_name

Manager_emp_id is the employee_id of this individual's manager.

I've tried using sub-queries to find managers and managers who don't have managers below them, which takes forever and a day to find (out of like 8,000 employees total)

Needless to say, I need to find a better way of finding these managers.

Sample data:

employee_id manager_emp_id Employee_name
111111111 NULL Mr. President
222222222 111111111 Frank Smith
333333333 222222222 Guy Thornton
444444444 222222222 Bill King
555555555 444444444 Mike Black
666666666 444444444 John Reynolds

The query would return the following results:

333333333 222222222 Guy Thornton
444444444 222222222 Bill King


ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[SantaMufasa]

Wholsea,

This query shows who has no direct reports. It is extremely fast:

select employee_id, manager_emp_id, employee_name
from employees emp_a
where not exists (select 'x' from employees where emp_a.employee_id = manager_emp_id)

Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Wholsea]

I'm not looking for individuals who do not have any direct reports, I am looking for individuals who have direct reports, but none of those direct reports have reports of their own.

If you look at the example above, the returned results show two individuals who have people who report to them, but no one reports further down the tree..

I have a query like this:

select employee_id, manager_emp_id, employee_name
from employees emp_a
where employee_id in (select distinct manager_emp_id from employees where emp_a.employee_id = manager_emp_id)
and employee_id not in (select distinct manager_emp_id
from employees emp_b
where employee_id in (select distinct manager_emp_id from employees where emp_b.employee_id = manager_emp_id))

Which works, but it is EXTREMELY SLOW when processing through 8,000 employee records...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

Found a solution...

Its not pretty, but it works...

SELECT DISTINCT manager_emp_id employee_id
      FROM employees e
      WHERE manager_emp_id NOT IN (
        SELECT DISTINCT manager_emp_id
        FROM employees e2
        WHERE manager_emp_id = e.manager_emp_id
        AND employee_id IN (
            SELECT DISTINCT manager_emp_id
            FROM employees
            WHERE manager_emp_id = e2.employee_id))

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Wholsea]

I had one using a connect by prior working at some of the lower levels of the org structure, but when it found some of the managers up higher, it ran into circular references which caused problems with the DB.

This seems to work rather quickly


ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise