如何根据记录可能共享值的多个字段排除记录

Question

我有两个地址表。它们的格式非常相似，[主]表也具有客户名称

CREATE TABLE [shipTo] 
(
    [id] int IDENTITY(1,1),
    [custID] int,
    [address1] varchar(255),
    [address2] varchar(255),
    [city] varchar(255),
    [state] varchar(255),
    [zip] varchar(255)
)

Shipto 表为每个主要客户提供许多地址，并且许多次主要地址也列为 shipto 。我不想在结果中包括该主要地址，但是我无法正确工作，因为在许多情况下，其他地址可能会共享城市或州，甚至是空白地址2。

这是我所拥有的...

SELECT 
    @tmpCustID = [id], 
    @chkAddress1 = [Address 1], 
    @chkAddress2 = [Address 2], 
    @chkCity = [City], 
    @chkState = [State],  
    @chkZip = [Zip] 
FROM 
    [main] 
WHERE 
    [id] = @cnt

SELECT * 
FROM [shipTo]  
WHERE [custID] = @tmpCustID
  AND [Address 1] <> @chkAddress1
  AND [Address 2] <> @chkAddress2
  AND [City] <> @chkCity
  AND [State] <> @chkState
  AND [Zip] <> @chkZip

这是数据：

主要的

|id| address1     | address2      | city     | state | zip   |
|2 | 123 baker st |               | columbus | oh    | 43081 |

shipto，

|id| custid| address1       | address2       | city         | state | zip   |
|1 | 2     | 123 baker st   |                | columbus     | oh    | 43081 |
|2 | 2     | 626 oak point  |                | cleveland    | oh    | 43092 |
|3 | 2     | 17 purple blvd |                | columbus     | ga    | 81265 |
|4 | 2     | 851 brawny     |                | grand rapids | mi    | 49417 |
|5 | 2     | 101 olive rd   | suite #3       | grand rapids | mi    | 49417 |

我希望它能返回ID 2-5，但它仅返回＃5，因为它与Shipto的主要元素相匹配，但我需要它查看整体记录。

我还尝试添加一个和括号的想法认为它会起作用，但这也不是。

SELECT * 
FROM [shipTo]  
WHERE [custID] = @tmpCustID
  AND ([Address 1] <> @chkAddress1
  AND [Address 2] <> @chkAddress2
  AND [City] <> @chkCity
  AND [State] <> @chkState
  AND [Zip] <> @chkZip)

Answer 1

也许尝试此查询

SELECT * FROM [shipTo]  
WHERE [custID] = @tmpCustID
AND NOT
([Address 1] = @chkAddress1
 AND [Address 2] = @chkAddress2
 AND [City] = @chkCity
 AND [State] = @chkState
 AND [Zip] = @chkZip)