复杂查询连接检查值不存在 [英] complex query join checking that value does not exist

问题描述

我正在努力应对查询的巨大挑战.我有两张桌子，第一个有

I am struggling with a great challenge of a query. I have two tables, first has

Tb1身份证预定日期rteID

Tb1 drID schedDate rteID

第二个:

Tb2身份证名称名称活跃

Tb2 drID FName LName Active

Tb1 drID 必须检查 Null 或空白并匹配 schedDate 和 drID 不能有任何与 Tb2.drID 匹配所选日期的值，检查 Null 和 '' 基本上就是这样做的.

Tb1 drID must be checked for Null or blank and match on schedDate and drID can not have any values that match Tb2.drID for date selected, checking for Null and '' essentially do this.

SELECT drID, schedDate, rteID
FROM            Tb1
WHERE        (drID IS NULL OR drID = '') AND (schedDate = 11 / 1 / 2012)

根据所有这些，我需要从 TB2 drID、Fname、LName 返回，其中 Active = True 并且 drID 不存在于 tb1 中所选 schedDate 的任何记录中.

From all of this I need to return from TB2 drID, Fname, LName Where Active = True and drID does not exist on any record in tb1 for the schedDate selected.

对于任何可能的日期，都有许多 tb1 rteID 记录.

There are many tb1 rteID records for any possible date.

感谢您对此提供的任何帮助，感谢您的大力支持.

Thank you for any help on this and huge Holiday Thank You.

推荐答案

你能否让你的 select 语句成为子查询，例如:

Can you make your select statement a subquery for example:

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
 AND   drID NOT IN (
       SELECT drID
       FROM   Tb1
       WHERE  (drID IS NULL OR drID = '')
        AND  (schedDate = 11 / 1 / 2012)
       )

编辑
处理 schedDate 为空的情况，然后

Edit
To handle the case that the schedDate is null then

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
 AND   drID NOT IN (
       SELECT drID
       FROM   Tb1
       WHERE  (drID IS NULL OR drID = '')
        AND  (schedDate = @yourDate OR schedDate IS NULL)
       )

编辑 2
要处理 drID 为空的情况，您可以使用 NOT EXISTS 方法，如这篇关于 不存在与不存在

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
 AND   NOT EXISTS (
       SELECT drID
       FROM   Tb1
       WHERE  (schedDate = @yourDate) 
        AND   Tb1.drID = TB2.drID
       )

这篇关于复杂查询连接检查值不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！