SQL Select: GROUP BY Having Count

[Brian R. Wainwright]

Hey everyone,

I'm having difficulty resolving a query in Oracle 8i...

I have the following table:

CREATE TABLE COMPANY_EMPLOYEES
(
COMPANY_ID INTEGER DEFAULT NULL,
EMPLOYEE_ID INTEGER DEFAULT NULL,
MODULE_TYPE INTEGER
)
The primary key is a combination of all 3 columns.
The module_type column can be one of four values: 4, 5, 6 or 7.
I'm attempting to select those employee_ids that exist in ALL 4 module_types for a given number of company_id's. For example, given the following data:

company_id     employee_id    module_type
16                2              4
51                1              5
51                1              6
51                2              6

The following query returns employee_id 1

 SELECT employee_id FROM COMPANY_EMPLOYEES
WHERE company_id IN (51) 
GROUP BY employee_id HAVING COUNT(employee_id) > 1;   

This query works great. I need to further filter the result set however, by module_type so that if no record exists for a given module type, no results are returned. In other words, in order to obtain a result set there must be at least 1 row for each module_type.

Does anyone have any suggestions for further filtering this result set? Thanks!
[ June 01, 2006: Message edited by: Brian R. Wainwright ]

[stu derby]

For your further restricted query, try this:

SELECT employee_id 
FROM COMPANY_EMPLOYEES
WHERE company_id IN (51)
GROUP BY employee_id
HAVING COUNT(employee_id) = 4; 

4 modules means 4 rows for each employee of that company.

[Brian R. Wainwright]

Thanks stu...

I've actually refined the query a little further...

    
SELECT employee_id FROM company_employees ugd 
WHERE  company_id IN (51)
AND (SELECT COUNT(value)FROM ATTRIBUTE_VALUES ugv
WHERE ugv.company_id = ugd.company_id AND ugv.value = 'TRUE') =
(SELECT COUNT(DISTINCT module_type) FROM company_employees 
WHERE company_id = ugd.company_id)
GROUP BY employee_id HAVING COUNT(employee_id) = ?
 

So this is doing something similar, but checking another table (ATTRIBUTE_VALUES) to get a count of the number of rows with a value of 'TRUE'

company_id     module_type    value
51                4              TRUE
51                5              TRUE
51                6              TRUE
51                7              TRUE

This number is used to make certain that the number of the number I get in the second sub-select is valid. The last piece here is to pass that value into the group by clause like this:

SELECT employee_id FROM company_employees ugd 
WHERE  company_id IN (51)
AND (SELECT COUNT(value)FROM ATTRIBUTE_VALUES ugv
WHERE ugv.company_id = ugd.company_id AND ugv.value = 'TRUE') =
(SELECT COUNT(DISTINCT module_type) FROM company_employees 
WHERE company_id = ugd.company_id)
GROUP BY employee_id HAVING COUNT(employee_id) = 
  (SELECT COUNT(value)FROM ATTRIBUTE_VALUES ugv
    WHERE ugv.company_id = ugd.company_id AND ugv.value = 'TRUE')

The problem with this last statement is that I get a "NOT A GROUP BY Expression" when adding the last select. Oracle doesn't like the " = ugd.company_id". If I replace the = ugd.company_id with a number (51) it works. This number however needs to be whatever the current ugd.company_id is. If I got this to work, I guess I also wouldn't like that it does the same query twice. But I could live with that. I know I should be able to have a dynamic value as part of my HAVING COUNT(employee_id) = clause, but I'm not sure if it is possibel in this context. Any more ideas would be great! Thanks again!
[ June 02, 2006: Message edited by: Brian R. Wainwright ]

[Brian R. Wainwright]

Ok... well I think I figured out the problem... pretty simple actually. I just need to add ugd.company_id to my GROUP BY clause... I guess because it is being included as part of the SELECT somehow? I not really certain why. Does anyone have an explanation?