SQLTeam.com | Weblogs | Forums

Inner Join with multiple filters

Table 1
Bank ID Transaction ID Date Amount
1 1 30-06-2020 50
1 2 30-06-2020 80
1 1 31-12-2020 72
1 2 31-12-2020 71
2 1 30-06-2020 70
2 2 30-06-2020 100
2 1 31-12-2020 67
2 2 31-12-2020 82
Table 2
Bank ID Transaction ID Date Amount
1 1 31-03-2021 83
1 2 31-03-2021 88
Required Table
Bank ID Transaction ID Date Amount
2 1 31-03-2021 67
2 2 31-03-2021 82

Can some help me with the query

Select distinct t1.Bank ID, t1.Transaction ID, t1.Date, t1.Amount from Table 1 t1 INNER JOIN Table 2 t2 ON t2.Bank ID <> (select distinct (Bank ID) from Table 2 where Date >= (select max(Date) from Table 2))

I would use a NOT EXISTS or IN statement here:

EXISTS (Transact-SQL) - SQL Server | Microsoft Docs