Find records that do not exist in 2nd table, and also the values mapped against them from first-CodePudding

Hi so this question has been asked before, but I am not only just trying to extract the records that do not just exist in the first table, but also the mapping from the first table where they don't?

Eg.

Assuming I will join using Month and Year--

Table 1:

Month	Year
3	2021
4	2021
5	2021
6	2023

Table 2:

ID	Month	Year
11	3	2021
22	4	2021
33	5	2021

Output:

ID	Month	Year
11	6	2023
22	6	2023
33	6	2023

CodePudding user response：

Select  
    t2.ID "ID",  
    no_match.MONTH "MONTH",  
    no_match.YEAR "YEAR"  
From  
    Table2 t2  
Inner Join  
    ( Select t1.MONTH "MONTH", t1.YEAR "YEAR" From Table1 t1 Left Join Table2 t2 ON(t1.MONTH = t2.MONTH And t1.YEAR = t2.YEAR) Where t2.YEAR Is Null ) no_match ON(1=1)  
Where  
    t2.YEAR || t2.MONTH <> no_match.MONTH || no_match.YEAR

CodePudding user response：

From what you say and show, you want to cross join all table 2 rows to the table 1 rows that don't have a match in table 2. For the lookup you can use NOT EXISTS.

select t2.id, t1.month, t1.year
from t1 cross join t2
where not exists
(
  select null
  from t2
  where t2.year = t1.year and t2.month = t1.month
);