Merge two fields from one table with one field from another table

Transact-SQL
1

I have one table that includes a date column and an exchange rate column
below is table1

date rate
01/01/2023 4.00
02/01/2023 4.01
03/01/2023 4.02

i I have another table that includes two dates. Date of purchase and date of sale
below is table2

buy sale
01/01/2023 02/01/2023
02/01/2023 03/01/2023

The required query will display buy date, sale date, buy rate and sale rate

below is the required query

buy date sale date buy rate sale rate
01/01/2023 02/01/2023 4.00 4.01
02/01/2023 03/01/2023 4.01 4.02

2 
SELECT 
    bs.Buy,
    bs.Sale,
    fx1.rate AS BuyRate,
    fx2.rate AS SaleRate
FROM
    BuySaleTable AS bs
    LEFT JOIN fXRateTable AS fx1 ON
        fx1.date = bs.BuyDate
    LEFT JOIN fxRateTable AS fx2 ON
        fx2.date = bs.SaleDate;
1 Like
3

thank you