SQL Server 查询，用于查找没有关联的数据透视表之间的记录

Question

>我正在尝试编写一个查询，该查询从两个表中返回记录列表，其中两个表没有绑定每个表的记录，或者找到一个标志表示不绑定记录。

例如：

房子

╔════╦══════════════╦
║ Id ║  Name        ║
╠════╬══════════════╬
║  1 ║ 10 Maple     ║
║  2 ║ 20 Oak       ║
║  3 ║ 30 Drive     ║
╚════╩══════════════╩

房屋居住者

╔════╦══════════════╦═════════════╦════════════════╗
║ Id ║  HouseId     ║ OccupentId  ║ LeftOn         ║
╠════╬══════════════╬═════════════╬════════════════╣
║  1 ║ 1            ║  1          ║ NULL           ║
║  2 ║ 1            ║  1          ║ 2019-01-01     ║
║  3 ║ 3            ║  2          ║ 2019-01-01     ║
╚════╩══════════════╩═════════════╩════════════════╝

我正在尝试编写一个SQL查询来查找"空"房屋。 例如，没有居住者的房屋或离开的居住者。最终结果将是：

╔════╦══════════════╦
║ Id ║  Name        ║
╠════╬══════════════╬
║  2 ║ 20 Oak       ║
║  3 ║ 30 Drive     ║
╚════╩══════════════╩

请注意，1号房屋不在此列表中，因为即使居住者离开，他又回来了。

到目前为止，我的查询(非常关闭(如下所示：

SELECT *
FROM Houses H
INNER JOIN HousesOccupants HO ON HO.HouseId = H.Id
WHERE NOT EXISTS (
SELECT *
FROM HousesOccupants
WHERE HousesOccupants.LeftOn IS NULL
AND HO.HouseId = H.Id
)
OR (HO.Id IS NULL)

如何修复以下查询以返回正确的数据集？

Answer 1

您的查询存在多个问题：

• not exists中的子查询与外部查询不相关

• 即使是这样，它也会归还居住者离开的房屋，但不会归还从未有过居住者的房屋：这些房屋被INNER JOIN淘汰了。请注意，ORDed条件是无操作的，因为INNER JOIN再次消除了从未有过居住者的房屋

我只会使用not exists来过滤houseOccupant中不存在记录的房屋，leftOnnull：

select h.*
from houses h
where not exists (
select 1
from houseOccupants ho
where ho.houseId = h.id and ho.leftOn is null
)