SQL查询来获得不从表

Question

存在的记录我有4个表
表1
用户ID（PK）----------用户名
1 ----------- ------- ABC
2 ------------------- PQR

表2
CUSTID（PK）-------- - 客户名称
1 -----------------------客户1
2 ------------------ ----- Cust2
3 ----------------------- Cust3

表3
CustId（FK）---------- UserId（FK）
1 ----------------------- 1
2 ----------------------- 2

表4
OfficeId（PK）---------- OfficeName -------- CustId（Fk）
1 ------------------------ Off1 ---------- --------- 1
2 ------------------------ Off2 ------------ ------- 1
3 ------------------------ Off3 -------------- ----- 2

Tabl5
OfficeId（FK）---------- UserId
1 ------------------------- 1
3 ------------------------- 2

问题是当用户与3 Cust关联时，但他被分配了属于只有2个Cust，那么它应该返回未分配的officeId？ 从上面的表，
当我通过用户ID = 1到我的存储过程
欲以下输出

OfficeId --------- OfficeName
2 --------- --------关闭2

Answer 1

我不想跟踪哪些表是哪的，所以我使用的表名是更有意义（我）......

使用not exists()

select o.OfficeId, o.OfficeName 
from users_customers uc 
    inner join office o 
    on uc.CustId = o.CustId 
where uc.UserId = @UserId 
    and not exists (
    select 1 
    from users_office uo 
    where uo.UserId = @UserId 
     and uo.OfficeId = o.OfficeId 
) 

使用except（这也将删除重复的结果）

select o.OfficeId, o.OfficeName 
from users_customers uc 
    inner join office o 
    on uc.CustId = o.CustId 
where uc.UserId = @UserId 

except 

select o.OfficeId, o.OfficeName 
from users_office uo 
    inner join office o 
    on uo.OfficeId = o.OfficeId 
where uo.UserId = @UserId 

Answer 2

SELECT OfficeId, OfficeName 
FROM Table4 
WHERE OfficeId NOT IN (
    SELECT Table4.OfficeId 
    FROM Table3 
    INNER JOIN Table4 
     ON Table3.CustId = Table4.CustId 
    INNER JOIN Tabl5 
     ON Tabl5.UserId = Tabl3.UserId 
     AND Tabl5.OfficeId = Table4.OfficeId 
);