How to check if records are available in another table and print the result with new column in SQL s-CodePudding

I have two tables as follow

Table 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

Table 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

I need to check whether table 1 records are available in table 2 records and create a new column called Available with yes or no

It needs to be match all the columns by each row

Expected output:

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

CodePudding user response：

You can use something like below

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