比较两个表，并返回行不存在基于多个领域比较

Question

说我有一个表称为#ListTable：

Item: 
Apple 
Bannana 
Pea 
Milk 
Green 

而另一个表食品类似于：

Date  Fruit  Vegetable  Dairy  Color 
2/16/17 Apple  NULL   NULL  Green 
2/16/17 NULL  Pea   NULL  Green 
2/16/17 NULL  NULL   Milk  White 

如果列表中的值存在于Food Table中，我想返回记录。

所以我有这样的：

DECLARE @Date = '2/16/17', 
@InclusionFlag = 1 

SELECT * 
FROM Food F 
INNER JOIN #ListTable LT1 
ON F.Fruit = LT1.Item 
WHERE Date = @Date 

UNION ALL 

SELECT * 
FROM Food F 
INNER JOIN #ListTable LT2 
ON F.Color = LT1.Item 
WHERE Date = @Date 

... 

本质返回记录，如果在列表中的项目对应于我指定的领域之一。我知道苹果可能会有“重复”的记录是“苹果”和“绿色”，我想要。

但是，我也希望能够将包含标志切换为0并返回ListTable中与Food中的任何字段都不匹配的项目。

我将如何返回不匹配的证券列表？我知道有类似的问题，但我很难找出多场比较案例。

感谢

Answer 1

您可以使用cross apply()先UNPIVOT您的数据，然后用case表达when exists()在where子句返回0，或者当它的存在和比较，为@InclusionFlag 1。

;with FoodItem as (
    select x.Item 
    from food as f 
    cross apply (values (Fruit),(Vegetable),(Dairy),(Color)) as x (Item) 
    where x.Item is not null 
    and f.Date = @Date 
) 

select 
    lt.Item 
from ListTable as lt 
where case when exists (
    select 1 
    from FoodItem fi 
    where lt.Item = fi.Item 
) then 1 else 0 end = @InclusionFlag 

Answer 2

如果我是正确的，食品行（可重复）匹配#ListTable

SELECT F.* 
FROM Food F 
INNER JOIN #ListTable LT1 
ON LT1.Item IN (F.Fruit, F.Vegetable, F.Dairy, F.Color) 
WHERE Date = @Date