在MSSQL中加入Query的帮助 [英] Help required for Join Query in MSSQL

问题描述

SELECT     SampleRegistration.SampleNumber
FROM         SampleRegistration INNER JOIN
                      NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID INNER JOIN
                      Protocol ON SampleRegistration.SampleNumber = Protocol.SampleNumber
WHERE     (NewSampleEntrys.RecvdDate > '1/1/2013') AND (SampleRegistration.Tested = 'F')

In以上查询我想要NewSampleEntrys&的所有匹配记录。 SampleRegistration并跳过Protocol表中的所有记录。



我应该做什么改变plz帮助。

In above Query i want all matching record from NewSampleEntrys & SampleRegistration and skip all record in Protocol table.

what changes should i made plz help.

推荐答案

您可以尝试使用NOT IN运算符..

You can try using NOT IN operator..

SELECT     SampleRegistration.SampleNumber
FROM         SampleRegistration INNER JOIN
                      NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID
WHERE     (NewSampleEntrys.RecvdDate > '1/1/2013') AND (SampleRegistration.Tested = 'F') AND SampleRegistration.SampleNumber Not IN (SELECT SampleNumber From Protocol)

如果你想跳过协议中没有SampleRegistration匹配的记录，你应该使用LEFT JOIN：

If you want to skip records in Protocol that do not have a match in SampleRegistration, you should use a LEFT JOIN:

SELECT       SampleRegistration.SampleNumber
FROM         SampleRegistration 
INNER JOIN   NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID 
LEFT JOIN    Protocol ON SampleRegistration.SampleNumber = Protocol.SampleNumber
WHERE        (NewSampleEntrys.RecvdDate > '1/1/2013') 
AND          (SampleRegistration.Tested = 'F')

解决方案2有效，但速度很慢。请参阅 http://www.componentworkshop.com / blog / 2009/06/26 / sql-server-basics-avoid-in-and-not-in [ ^ ]了解更多关于为什么不想使用IN或不在。您可以保留原始连接，但通过检查空值来添加条件，即未找到协议中的记录：

Solution 2 works, but is slow. See http://www.componentworkshop.com/blog/2009/06/26/sql-server-basics-avoiding-in-and-not-in[^] for more information on why you don't want queries with IN or NOT IN. You can keep your original join, but add a condition that no record in Protocol was found by checking for null values:

SELECT       SampleRegistration.SampleNumber
FROM         SampleRegistration
INNER JOIN   NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID
LEFT JOIN    Protocol ON SampleRegistration.SampleNumber = Protocol.SampleNumber
WHERE        (NewSampleEntrys.RecvdDate > '1/1/2013')
AND          (SampleRegistration.Tested = 'F')
AND          (Protocol.Id IS NULL)

假设Id是Protocol表中的现有Id列。如果它的名称不同，请更新条件以反映这一点。

Assuming Id is an existing Id column in the Protocol table. If it's named differently, please update the condition to reflect this.

这篇关于在MSSQL中加入Query的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！