getting the employees that work on project 1 AND 2

  • Thread starter Thread starter Alain
  • Start date Start date
A

Alain

Hi,

Let's say I have these tables:

Employees (EmployeeId, Name)
Projects (ProjectId, Name)
ProjectMembers (ProjectId, EmployeeId)

Now I want to get a list of employees that work on BOTH project 1 AND
2. If a person only works on 1 then it should not be in the list.

Sounds like a simple query, but at this moment I am about:blank ;)
Anyone with some bright ideas?

Regards,

Alain
 
Assumption:
The combination of an employee and a project is unique for all records in
ProjectMembers

SELECT Employees.EmployeeId, Employees.Name
FROM Employees INNER JOIN ProjectMembers
ON Employees.EmployeeId = ProjectMember.EmployeeID
WHERE ProjectMember.ProjectID in (1,2)
GROUP BY Employees.EmployeeID, Employees.Name
HAVING Count(Employees.EmployeeID) = 2

If ProjectMembers can have the same combination of EmployeeId and ProjectID
more than once then the above can give you incorrect results.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Store Employee ID in table, Display User Name in form 1
Excel VBA - counting cells by colors and names to it in two different rows. 0
must have this and that 4
Update query / Operation must use an updateable query error messag 4
Automatically fill-in a box 5
Query with two Tables with date filed for Date Range 1
Query with percetnages.. How do I? 2
Create query to merge 2 tables? 2

Back
Top