SQL：根据表B中的列值查找表A中没有表B中值的所有记录

Using Microsoft SQL Server 2012, I'm trying to get all businesses who did not make a payment in 2016.

使用Microsoft SQL Server 2012，我试图让所有在2016年没有付款的企业。

Here is my schema

这是我的架构

Business Table
---------------
Id     Name
1      Business A
2      Business B

Payments Table
---------------
Id     BusinessId   Year
1      2            2016
2      1            2017
3      2            2017

I need a SQL statement that returns Business A since it does not have a payment for 2016.

我需要一个返回Business A的SQL语句，因为它没有2016年的付款。

Here is what I have tried:

这是我尝试过的：

SELECT [Business].Name
FROM Businesses as [Business]            
Left Outer JOIN Payments as [Payment]
     ON [Payment].BusinessId = [Business].Id
Where [Business].Id is null and [Payment].TaxYear = 2016

Any help would be greatly appreciated.

任何帮助将不胜感激。

Also, the title might be a little convoluted, so a suggested edit for the title of this question would be welcome as well.

此外，标题可能有点复杂，因此建议编辑此问题的标题也是受欢迎的。

2 个解决方案

#1

NOT EXISTS, should return Business A since it does not have a 2016 transaction

不存在，应返回商业A，因为它没有2016年的交易

SELECT B.Name
FROM Business as B            
WHERE NOT EXISTS (    
  SELECT 1 FROM Payments P
Where P.TaxYear = 2016
     AND P.BusinessId = B.Id )

#2

I think you are looking for the combination of businesses and years that are not in the payments table. If so:

我认为您正在寻找不在付款表中的业务和年份的组合。如果是这样：

select b.*
from businesses b cross join
     (select distinct year from payments) y left join
     payments p
     on b.id = p.businessid and b.year = y.year
where p.businessid is null;

The cross join generates all combinations of businesses and years. The left join and where clause then finds the ones that don't exist.

交叉连接生成所有业务和年份的组合。然后左连接和where子句找到不存在的连接。

If you are only looking for one year, then @maSTAShuFu answer is the correct approach.

如果您只寻找一年，那么@maSTAShuFu答案是正确的方法。

#1