Clever Geek Handbook

Searching for all users that appear in all departments of SQL Server?

I have a table Users

I also have a table Departments

And I have a table of cards between Users

and Departments

.

I want to find all users name and Id that show up in all departments .

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

It will only appear if it is listed in all departments (1..5)

I started doing something very long (with a lot of lengths) using temp table and I guess there is a better way.

I am creating Sql Fiddle as well .

+3


source to share


6 answers


There are several ways to do this.

You can require that the number of departments the user is in is equal to 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 can require that removing user departments from the list of all departments be left with 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.

+1


source


try it

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)

This will create



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

And sqlfiddle

+1


source


You can do it like this:

select  u.* 
from    Users u
where not exists
(
    select  1 
    from    Departments d 
    where   not exists
    ( 
      select  1 
      from    MapUserstoDepartments m 
      where   d.depId = m.depId
      and     m.userId = u.userId
     )
)

SQL Fiddle

+1


source


Is this what you want?

Select Tbl.userID , Tbl.username from (Select u.userid , u.username ,  
count(u.userid)  as Count from MapUsersToDepartments m
inner join Users u on m.UserID = u.userID
group by u.userid , u.username)Tbl
where Tbl.Count = (Select count(*) from Departments)

Here is a violin

http://sqlfiddle.com/#!3/5a960/53

0


source 


select
  Users.userId,
  count(Departments.depId),
  count(MapUserstoDepartments.userId)

from
  Users
  left join MapUserstoDepartments on MapUserstoDepartments.userId = Users.userId
  left join Departments on Departments.depId = MapUserstoDepartments.depId


group by 
  Users.userId

having
  (SELECT COUNT(*) from Departments) = count(MapUserstoDepartments.userId)
0


source


Try to execute the request. Let me know if it helps

 select * from users where userid in
       (select userid from MapUserstoDepartments
       group by userid
        having count(userid) = 5)
0


source






More articles:


All Articles