mysql "and" logic within result set

Question

Say I have a data set like the following:

table foo

id | employeeType | employeeID
-------------------------
 1 | Developer    | 1
 2 | Developer    | 2
 3 | Developer    | 3
 4 | Manager      | 1
 5 | Manager      | 4
 6 | Manager      | 5
 7 | CEO          | 1
 8 | CEO          | 6

and I wanted to run a query that would return all the employeeids (along with the employeeTypes) where there is a common employee id between all employeeTypes (that's the 'and' logic. ONly employeeIDs that have all employeeTypes will return. employeeType = Developer and employeeType=Manager and employeeType=CEO). For the data above the example output would be

result table

id | employeeType | employeeID
-------------------------
 1 | Developer    | 1
 4 | Manager      | 1
 7 | CEO          | 1

I was able to do this when I only had only TWO employeeTypes by self joining the table like this.

select * from foo as fooOne
join foo as fooTwo
on fooOne.employeeID = fooTwo.employeeID
AND
fooOne.employeeType <> fooTwo.employeeType

that query returns a result set with values from fooTwo when the 'and' logic matches, but again, only for two types of employees. My real use case scenario dictates that I need to be able to handle a variable number of employeeTypes (3, 4, 5, etc...)

Any thoughts on this would be greatly appreciated.

Answer 1

This should return the rows that you want:

SELECT foo.*
FROM
  foo
WHERE
  employeeID IN (
    SELECT employeeID
    FROM foo
    GROUP BY employeeID
    HAVING COUNT(DISTINCT employeeType) =
      (SELECT COUNT(DISTINCT employeeType)
       FROM foo)
  )

Please see a fiddle here.

The inner query will return the number of distinct employee types:

(SELECT COUNT(DISTINCT employeeType) FROM foo)

The middle query will return all the employee IDs that have the maximum number of employee types:

SELECT employeeID
FROM foo
GROUP BY employeeID
HAVING COUNT(DISTINCT employeeType) =
       (SELECT COUNT(DISTINCT employeeType) FROM foo)

and the outer query will return the whole rows.