Sql-server – Left join SQL Select Statement

selectsql servertable

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

Best Answer

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