SQL联接和按用户筛选-多个联接

我的SQL有问题，找不到解决方案。这似乎很简单。

我有四个表如下：Task、User、Building和BuildingUser。任务-每个用户都可以打开与建筑物相关的任务用户-存储用户建筑物-储存建筑物BuildingUser-表示用户和建筑之间的关系。它显示哪个用户可以"看到"一栋建筑。N->N

我的案例：

表任务

CREATE TABLE [dbo].[TASK] 
  ( 
     [ID]          [INT] IDENTITY(1, 1) NOT NULL, 
     [ID_USER]     [INT] NOT NULL, 
     [ID_BUILDING] [INT] NOT NULL 
  ) 

表建筑

CREATE TABLE [dbo].[BUILDING] 
  ( 
     [ID]   [INT] IDENTITY(1, 1) NOT NULL, 
     [NAME] [VARCHAR](50) NOT NULL 
  ) 

表用户

CREATE TABLE [dbo].[USER] 
  ( 
     [ID]   [INT] IDENTITY(1, 1) NOT NULL, 
     [NAME] [VARCHAR](50) NOT NULL 
  ) 

表构建用户

CREATE TABLE [dbo].[BUILDINGUSER] 
  ( 
     [ID]          [INT] IDENTITY(1, 1) NOT NULL, 
     [ID_BUILDING] [INT] NOT NULL, 
     [ID_USER]     [INT] NOT NULL 
  ) 

样本数据

任务

ID|ID_USER|ID_BUILDING
1 |1      |1
2 |1      |2

用户

ID|Name
1 |Carlos
2 |Joao
3 |Maria

建筑

ID|Name
1 |Rochavera
2 |San George

构建用户

ID_USER|ID_BUILDING
2      |1
3      |2

所以，我想把两个任务同时转移给尽可能多的用户。但是，这些用户必须查看我选择的任务的所有建筑。例如，如果我选择了Task.ID 1和2，那么查询必须返回"nothing"，因为我没有看到这两栋建筑的用户。但如果我只选择了一个任务，比如ID 1，我就可以转移到用户ID 2。

我有这个疑问，但它错了。这只是一次尝试。

SELECT DISTINCT [USER].ID, 
                [USER].NAME 
FROM   TASK 
       INNER JOIN BUILDINGUSER 
               ON BUILDINGUSER.ID_BUILDING = TASK.ID_BUILDING 
       INNER JOIN [USER] 
               ON [USER].ID = BUILDINGUSER.ID_USER 
WHERE  TASK.ID IN ( 1, 2 ) 

编辑我按照下面的说明进行操作。

SELECT u.ID, 
       u.NAME 
FROM   TASK t 
       JOIN BUILDINGUSER bu 
         ON bu.ID_BUILDING = t.ID_BUILDING 
       JOIN [USER] u 
         ON u.ID = bu.ID_USER 
WHERE  t.ID IN ( 1, 2 ) 
       AND u.ID != t.ID_USER 
GROUP  BY u.ID, 
          u.NAME 
HAVING Count(bu.ID_BUILDING) = (SELECT Count(*) 
                                FROM   TASK t2 
                                WHERE  t2.ID IN ( 1, 2 )) 

select u.id, u.name
from Task t join
     BuildingUser bu
     ON bu.ID_BUILDING = t.ID_BUILDING join
     [User] u
     ON u.ID = bu.ID_USER 
group by u.id, u.name
having sum(case when t.id = 1 then 1 else 0 end) > 0 and  -- Any rows with taskid = 1 ?
       sum(case when t.id = 2 then 1 else 0 end) > 0;     -- Any rows with taskid = 2 ?

having sum(case when t.id = 1 then 1 else 0 end) > 0 and
       sum(case when t.id = 2 then 1 else 0 end) > 0 and
       sum(case when t.id = 3 then 1 else 0 end) = 0;