SQL中的NOT IN条件 [英] NOT IN condition in SQL

问题描述

谁能在两列中告诉我SQL中NOT IN条件的确切语法.

Can anyone tell me the exact syntax for NOT IN condition in SQL on two columns.

这是我用VBA编写的查询.

This is my query written in VBA.

strNewSql = "SELECT distinct(tblRevRelLog_Detail.PartNumber), tblRevRelLog_Detail.ChangeLevel, tblRevRelLog_Detail.ID FROM tblRevRelLog_Detail LEFT JOIN tblEventLog ON tblRevRelLog_Detail.PartNumber = tblEventLog.PartNumber"

strNewSql = strNewSql & " WHERE (tblEventLog.PartNumber) Not In(SELECT tblEventLog.PartNumber FROM tblEventLog WHERE tblEventLog.EventTypeSelected = 'pn REMOVED From Wrapper') AND tblEventLog.TrackingNumber = """ & tempTrackingNumber & """ AND tblEventLog.TrackingNumber =  tblRevRelLog_Detail.RevRelTrackingNumber;"

我想更改此子查询，它应应用于两列的组合，如下所示:

I want to change this sub query like, it should apply on the combination of two columns as follows:

strNewSql = "SELECT tblRevRelLog_Detail.PartNumber, tblRevRelLog_Detail.ChangeLevel, tblRevRelLog_Detail.ID FROM tblRevRelLog_Detail LEFT JOIN tblEventLog ON tblRevRelLog_Detail.PartNumber = tblEventLog.PartNumber"

strNewSql = strNewSql & " WHERE (((tblEventLog.PartNumber, tblEventLog.PartNumberChgLvl) Not In(SELECT tblEventLog.PartNumber,tblEventLog.PartNumberChgLvl FROM tblEventLog WHERE tblEventLog.EventTypeSelected = 'pn REMOVED From Wrapper') AND tblEventLog.TrackingNumber = """ & tempTrackingNumber & """ AND tblEventLog.TrackingNumber =  tblRevRelLog_Detail.RevRelTrackingNumber);"

但这不起作用.....

But this is not working.....

推荐答案

IN不能用于多于一列，但通常可以使用EXISTS达到相同的效果:

You can't use IN with more than one column but you can usually achieve the same effect using EXISTS:

SELECT *
FROM tbl1
WHERE NOT EXISTS
(
    SELECT *
    FROM tbl2
    WHERE tbl2.col1 = tbl1.col1
        AND tbl2.col2 = tbl1.col2
)

这篇关于SQL中的NOT IN条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！