MySQL query for ALL search terms in index

Question

I have a table of employees and their schedule, like so:

Emp_Name | Date
--------  -----
Smith    | 08-01-2009
Jones    | 08-01-2009
Goodman  | 08-02-2009
Smith    | 08-02-2009
Jones    | 08-02-2009
Goodman  | 08-03-2009

How would I write a query so that the results were only employee names of employees working on 08-02-2009 and 08-03-2009.

I'm getting caught up because all I can think of are ways to get the names for EITHER match, but I can't seem to find the right way to get the results for only the names that have matches for all search criteria across multiple rows.

So based on the example conditions, I should only get Goodman. But if I do a query like WHERE Date IS (list of dates) I would get Goodman and Smith and Jones, since Smith and Jones both work on 08-02-2009. If I try to do some kind of JOIN, I would not always end up with equal columns, since the number of days each employees works is variable.

I thought Union might be the way to go, but I wouldn't always know how may conditions someone was searching by.

Answer 1

Here's my first stab at it, there's probably a more efficient way than using HAVING though...

SELECT Emp_Name,COUNT(DISTINCT date) as daycount 
FROM employee 
WHERE date IN ('08-01-2009', '08-02-2009')
GROUP BY Emp_Name
HAVING daycount=2

Answer 2

If you don't need a generic solution, you can use this:

select Emp_Name
from employee
where Emp_Name
  in (select Emp_Name from employee where Date = '08-02-2009')
  and in (select Emp_Name from employee where Date = '08-03-2009')
  ...