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

Question

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

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。我不想在我的结果中包含主地址，但我不能让它正确工作，因为在许多情况下，其他地址可能共享一个城市或州，甚至是一个空白地址。

这是我的…

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，因为它匹配主地址的一些元素到船，但我需要它看到整个记录。

我还尝试添加AND和括号，认为它会工作，但这也没有。

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)