Finding all users which appears in ALL departments in SQL server?

Question

I have Table Users

I also have Table Departments

And I have a map table between Users and Departments.

I want to find all users name and Id which appears in all departments.

for example if there's 5 departments and Paul is only in department #1 , so Paul will not be in the output list.

He would , only if he is listed in all departments (1..5)

I started doing something which is very long (readly long) using temp table and I assume there is a better way.

I also create a Sql Fiddle.

Answer 1

There's more than one way of doing this.

You could require that the number of departments that the user is in equals the total number of departments:

SELECT
    * 
FROM
    Users
    INNER JOIN 
    (
        SELECT userId, COUNT(*) c FROM MapUserstoDepartments
        GROUP BY userId
        HAVING COUNT(*) = (SELECT COUNT(*) FROM Departments)
    ) UsersInAllDepartments
    ON Users.userId = UsersInAllDepartments.userId

You could require that removing the user's departments from the list of all departments leaves nothing:

SELECT * 
FROM Users
WHERE NOT EXISTS 
  (
    SELECT depId FROM Departments
    EXCEPT
    SELECT depId FROM MapUserstoDepartments WHERE userId = Users.userId
  )

I'm sure there are others.

Answer 2

Try this

SELECT u.userId, u.UserName 
FROM MapUserstoDepartments m INNER JOIN
     Users u ON u.userId = m.userId
GROUP BY u.userId, u.UserName 
HAVING COUNT(m.depId) = (SELECT COUNT(*) FROM Departments)

That will produce

| USERID | USERNAME |
---------------------
|    100 |     John |

And sqlfiddle