"IN" command's AND logical operator counterpart?

Question

Let's say I have 2 tables:

• Employee
• EmpLocXref

If I wanted to list employees that belong to locations 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)
;

Example.

But what if I wanted to list employees that only belong to locations 1, 2, and 3? I realize I could 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 on 2nd query. Added Fiddler examples.

Answer 1

The above actually wouldn't work, because you'd never have a record in EmpLocXref where the location_id is 1 and 2 and 3 all at the same time. You'd get zero results. Try this 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