Question: I am trying a simple (or not) query to get Users that exist in 2 Departments.

Struct:

**User**
ID
Name

**UserDepartment**
ID
IDUser
IDDepartment

**Department**
ID
Name

So i want users from DepartmentA and DepartmentB (

Is imposible to do:

```
Select * from User as US
left join UserDepartment as DP on User.ID = UserDepartment.IDUser
where DP.IDDepartment = 1 and DP.IDDepartment = 2

```

Zero results... 

An query to resolve this is sothing like: 

```
select * from UserDepartment
where IDDepartment in (1,2)
group by IDUser
having COUNT(*)=2

```

But is this the only solution? there are other easy queries out there?

Answer: 
To get all users that belong in both 1 and 2 departments:

```
SELECT * 
FROM User
WHERE User.ID IN (SELECT UserID FROM UserDepartment WHERE IDDepartment = 1)
    AND User.ID IN (SELECT UserID FROM UserDepartment WHERE IDDepartment = 2)

```

The results are essentially the same as the latter query you have, except it specifically looks for people who are in both "1" and "2", rather than anybody who has 2 or more department records that belong to "1" or "2" (doesn't include people with two "1" records but no "2" records).

If there's information from the userdepartment table you want included in the results, just change it to:

```
SELECT * 
FROM User US
INNER JOIN (SELECT * FROM UserDepartment WHERE IDDepartment IN(1,2)) DP ON US.ID = DP.UserID
WHERE User.ID IN (SELECT UserID FROM UserDepartment WHERE IDDepartment = 1)
    AND User.ID IN (SELECT UserID FROM UserDepartment WHERE IDDepartment = 2)

```
```
                /*** If only users for dept 1 and 2 need to be shown the use this query ****/
                Select * from #User u 
                inner join 
                (

                Select d1.UserID from #UserDepartment d1  
                where d1.Department = 1 
                intersect 
                Select d2.UserID from #UserDepartment d2
                where d2.Department = 2
                except 
                Select d2.UserID from #UserDepartment d2
                where d2.Department not in (1,2)
                )t on u.ID = t.UserID
             /***If the users that belong to dept 1 and 2 and can exist in 
                other depts then use this one**/
             Select * from #User u 
            inner join 
            (

            Select d1.UserID from #UserDepartment d1  
            where d1.Department = 1 
           intersect 
           Select d2.UserID from #UserDepartment d2
          where d2.Department = 2

              )t on u.ID = t.UserID

              /**To test use the following commented code**/
                /*Select * into #User from
                (
                Select 1 as ID, 'A' as Name
                union
                Select 2,'B'
                union
                Select 3,'C'
                union
                Select 4,'D'
                union
                Select 5,'E'
                union
                Select 6,'F'
                union
                Select 7,'G'
                union
                Select 8,'H'
                union
                Select 9,'I'
                union
                Select 10,'G'

                )t

                Select * into #Department from
                (
                Select 1 as ID, 'D1' as Name
                union
                Select 2,'D2'
                union
                Select 3,'D3'
                union
                Select 4,'D4'
                )t2

                Select * into #UserDepartment from 
                (
                Select 1 AS ID ,1 AS UserID ,1 AS Department
                union
                Select 2,1,2
                union 
                Select 3,1,3
                union
                Select 4,2,1
                union
                Select 5,3,2
                union
                Select 6,4,1
                union
                Select 7,4,2

                )t3
               */

```