CODEWITHSUNDEEP
sqldatabasems-access
PeterPan2020
PeterPan2020

Reputation: 172

Ms access DB - merge two tables base on columns

I am trying to merge two tables in one base on:

app.custid = fin.custid and
app.appdate = fin.findate and
app.price = fin.pricx

and what is not equal to create a new table. This two tables have no relationship.

Table app
appid   custid    appdate    price   
1         1       10/10/20     20    
2         2       10/10/20     10    
3         1       11/10/20     30    
4         3       12/10/20     20    
5         1       13/10/20     20    


Table fin
finid   custid    findate    pricex     
1         1       10/10/20     20
2         2       11/10/20     10
3         1       11/10/20     20
4         3       12/10/20     20
5         1       13/10/20     20

Derire output:

Table app-new
appid   custid    appdate    price    pricex
1         1       10/10/20     20       20     => same custid, appdate=findate, price=pricex
2         2       10/10/20     10       null   => same custid, appdate not the same findate
3         1       11/10/20     30       null   => same custid, appdate=findate, price is not the same pricex
4         3       12/10/20     20       20     => same custid, appdate=findate, price=pricex
5         1       13/10/20     20       20     => same custid, appdate=findate, price=pricex

Table fin-new
finid   custid    findate    pricex   
2         2       11/10/20     10
3         1       11/10/20     20

Upvotes: 1

Views: 62

Answers (1)

forpas
forpas

Reputation: 164214

For the 1st case you need a LEFT join of app to fin:

SELECT a.*, f.pricex
FROM app AS a LEFT JOIN fin AS f
ON f.custid = a.custid AND f.findate = a.appdate AND f.pricex = a.price

and for the 2nd case you can do it with NOT EXISTS:

SELECT f.*
FROM fin AS f
WHERE NOT EXISTS (
  SELECT 1
  FROM app AS a 
  WHERE f.custid = a.custid AND f.findate = a.appdate AND f.pricex = a.price
)

or with a LEFT join of fin to app that filters out the matched rows:

SELECT f.*
FROM fin AS f LEFT JOIN app AS a
ON f.custid = a.custid AND f.findate = a.appdate AND f.pricex = a.price
WHERE a.appid IS NULL

Upvotes: 1

Related Questions