The "IN" command and the logical operator?

Question

Let's say I have 2 tables:

• Employee
• EmpLocXref

If I wanted to display employees belonging to places 1, 2, or 3, I would write a query similar to:

SELECT DISTINCT e.id, e.name FROM Employee e JOIN EmpLocXref x ON x.employee_id = e.id WHERE x.location_id IN (1, 2, 3) ; 

An example .

But what if I would like to list employees who belong only to places 1, 2, and 3? I understand that I can write something similar to:

 SELECT e.id, e.name FROM Employee e JOIN EmpLocXref x ON x.employee_id = e.id WHERE x.location_id IN (1, 2, 3) GROUP BY e.id, e.name HAVING COUNT(*) = 3 ; 

Example

Is there a better way to do this that doesn't include dynamic SQL?

EDIT: Fixed derp moment in second request. Added Fiddler examples.

Answer 1

The above would not actually work, because you would never have an entry in EmpLocXref, where location_id is 1 and 2 and 3 at the same time. You will get zero results. Try instead:

 SELECT DISTINCT e.* FROM Employee e INNER JOIN EmpLocXref x1 ON e.ID = x1.EmployeeID AND x1.Location_id = 1 INNER JOIN EmpLocXref x2 ON e.ID = x2.EmployeeID AND x2.Location_id = 2 INNER JOIN EmpLocXref x3 ON e.ID = x3.EmployeeID AND x3.Location_id = 3