Sql查询从表中获取不存在的记录

I have 4 tables
Table1
UserId(PK)---------- UserName
1------------------ ABC
2-------------------PQR

我有四个表Table1 UserId(PK)- - - - - - - - - - -用户名1 - - - - - - - - - - - - - - - - - - ABC 2 - - - - - - - - - - - - - - - - - - pqr

Table2
CustId(PK)---------CustName
1-----------------------Cust1
2-----------------------Cust2
3-----------------------Cust3

表二CustId(PK)- - - - - - - - - -CustName 1 - - - - - - - - - - - - - - - - - - - - - - - cust1 2 - - - - - - - - - - - - - - - - - - - - - - - -Cust2 3 - - - - - - - - - - - - - - - - - - - - - - - -Cust3

Table3
CustId(FK)----------UserId(FK)
1-----------------------1
2-----------------------2

Table3 CustId(颗)- - - - - - - - - - - UserId(FK)队1 - - - - - - - - - - - - - - - - - - - - - - - 1 2 - - - - - - - - - - - - - - - - - - - - - - - 2

Table4
OfficeId(PK)----------OfficeName--------CustId(Fk)
1------------------------Off1-------------------1
2------------------------Off2-------------------1
3------------------------Off3-------------------2

Table4 OfficeId(PK)- - - - - - - - - - - OfficeName - - - - - - - - - - CustId(Fk)队1 - - - - - - - - - - - - - - - - - - - - - - - - Off1 - - - - - - - - - - - - - - - - - - 1 2 - - - - - - - - - - - - - - - - - - - - - - - -远比- - - - - - - - - - - - - - - - - - 1 3 - - - - - - - - - - - - - - - - - - - - - - - - Off3 - - - - - - - - - - - - - - - - - - 2

Tabl5
OfficeId(FK)----------UserId
1-------------------------1
3-------------------------2

Tabl5 OfficeId(颗)- - - - - - - - - - -标识1 - - - - - - - - - - - - - - - - - - - - - - - - 1 3 - - - - - - - - - - - - - - - - - - - - - - - - 2

The question is when User is associated to 3 Cust,but he is assigned offices belonging to only 2 of the Cust, then it should returns the unassigned officeId? From above tables,
When I pass UserId=1 to my stored procedure
I want following output

问题是，当用户与3个Cust关联，而他被分配的办公室只属于2个Cust，那么它应该返回未分配的officeId吗?从上面的表中，当我将UserId=1传递给存储过程时，我希望输出如下

OfficeId---------OfficeName
2-----------------Off2

OfficeId - - - - - - - - - - -OfficeName 2 - - - - - - - - - - - - - - - - -远比

2 个解决方案

#1

I do not want to keep track of which tables are which, so I am using table names that make more sense (to me)...

我不想跟踪哪个表是哪个表，所以我使用了更有意义的表名(对我来说)……

using 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
  )

using except (this will also remove duplicate results)

使用除外(这也将删除重复的结果)

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

#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
);

#1