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: 
This seems like a job for **INTERSECT**. You can get the list of user id's you want by doing something like:

```
SELECT IDUser FROM UserDepartment WHERE IDDepartment = 1

INTERSECT

SELECT IDUser 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
               */

```