query
Hi folks. Probably a very simple query but I cann't get the point. One tabel contains: - department_code - manager_code A manager can have MORE departments. The other table contains: - employee_nr - department_code Question: which manager controls the least nr. of employee's?. Any idea. Many thanks.
Henk J. Sanders wrote:
>
> Hi folks.
>
> Probably a very simple query but I cann´t get the point.
>
> One tabel contains:
> - department_code
> - manager_code
>
> A manager can have MORE departments.
>
> The other table contains:
> - employee_nr
> - department_code
>
> Question:
>
> which manager controls the least nr. of employee´s?.
SELECT manager_code, count(*) num_emp
FROM department d, employee e
WHERE d.department_code = e.department_code
GROUP BY 1
INTO TEMP fred;
SELECT manager_code, num_emp
FROM fred
WHERE num_emp = (
SELECT min(num_emp)
FROM fred);
Art S. Kagel
Henk J. Sanders wrote:
> Probably a very simple query but I cann´t get the point.
>
> One tabel contains:
> - department_code
> - manager_code
>
> A manager can have MORE departments.
>
> The other table contains:
> - employee_nr
> - department_code
>
> Question:
>
> which manager controls the least nr. of employee´s?.
Assume tables are Dept and Emp, then:
SELECT D.Manager_Code, COUNT(*) Employee_Count
FROM Dept D, Emp E
WHERE D.Department_code = E.Department_code
GROUP BY D.Manager_Code
INTO TEMP Manager_Counts;
SELECT Manager_Code
FROM Manager_Counts
WHERE Employee_Count =
(SELECT MIN(Employee_Count) FROM Manager_Counts)
I'm not sure whether you can somehow roll that into a single
SELECT by using a HAVING clause. I think not because you need
a MIN() or a COUNT() or nested aggregates, which are not allowed.
If two or managers all manage the same number of employees and
it is the smallest number of employees that anyboy manages, then
they will all show up in the last query. I guess you could use
a 'SELECT FIRST 1 Manager_Code' query, but it is not clear that
will always return the same answe.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>