SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE item (
  ID int NOT NULL PRIMARY KEY,
  price decimal(18, 3) NOT NULL,
  currency varchar(255) NOT NULL
);

create table rate (
  currency varchar(255) NOT NULL PRIMARY KEY,
  ratio decimal(16, 8) NOT NULL
);
INSERT INTO rate (currency, ratio)
VALUES
('RMB', 1),
('USD', 6.87),
('JPY', 0.05);

INSERT INTO item (ID, price, currency)
VALUES
(0, 10.23, 'RMB'),
(1, 2.49, 'USD'),
(2, 45.99, 'RMB'),
(3, 800, 'JPY');
7 rows affected

SELECT
 A.ID as ID,
 CAST(A.price * B.ratio as decimal(18, 3)) as price
FROM
 item A
LEFT JOIN
 rate B
on A.currency = B.currency;


ID
price


0
10.230

1
17.106

2
45.990

3
40.000