Left join SQL Select Statement

Question

Good day, Im trying to find a good way on my select statement

Table1
AccountID AccountName Rate OldRate
01 Smith X X2
02 Providence X X2
03 Jones X X3
04 Charity X2 X
05 Smith X3 X
06 Providence X X2
07 SMith X X3
08 Charity X X3
Table 2
Rate Amount 
X 500.00 
X2 200.00 
X3 30.00 
Result 
AccountID AccountName Rate Amount OldRate Amount
01 Smith X 500.00 X2 200.00
02 Providence X 500.00 X2 200.00
03 Jones X 500.00 X3 30.00

• Akina: Why records with AccountID in 4..8 are absent in the result?

Answer 1

You can try the following Command and make a left join on rate between both tables to get results as shown in your example

SELECT t1.AccountID, t1.AccountName, t1.Rate, t21.Amount, t1.OldRate, t22.Amount
FROM table1 t1
/* LEFT */ JOIN table2 t21 ON t1.Rate = t21.rate
/* LEFT */ JOIN table2 t22 ON t1.OldRate = t22.rate