如何检查另一个表中是否有记录，并在SQL server中打印新列的结果

Question

我有两个表如下

表1:

Product Type    Location    Sale Date
AB  Retail  California  6/13/2023 
AB  Sales   Los Angels  7/13/2023 
BC  Retail  Los Angels  8/13/2023 
CD  Sales   California  9/13/2023 
AB  Retail  Los Angels  9/13/2023 
EF  Retail  Los Angels  10/13/2023 

表2:

Product Type    Location    Sale Date
AB  Retail  California  6/13/2023 
AB  Sales   California  7/13/2023 
CD  Sales   California  9/13/2023 
AB  Retail  Los Angels  9/13/2023 
BC  Retail  Los Angels  9/13/2023 

我需要检查表1记录是否在表2记录中可用，并创建一个名为available的新列，带有yes或no

它需要每一行匹配所有列

预期输出:

Product Type    Location    Sale Date   Available
AB  Retail  California  6/13/2023   Yes
AB  Sales   Los Angels  7/13/2023   No
BC  Retail  Los Angels  8/13/2023   No
CD  Sales   California  9/13/2023   Yes
AB  Retail  Los Angels  9/13/2023   No
EF  Retail  Los Angels  10/13/2023  No

Answer 1

你可以在

SELECT ProductType,    Location,    SaleDate, CASE WHEN B.MatchRow>0 'Yes' ELSE 'No' END as Available
FROM TABLEA A
OUTER APPLY(
SELECT COUNT(*) AS MatchRow
FROM TABLEB  B
WHERE A.ProductType = B.ProductType AND A.Location = B.Location AND A.SaleDate = B.SaleDate
) B