CODEWITHSUNDEEP
sqlsql-servert-sqlvlookup
Jurie Wessels
Jurie Wessels

Reputation: 1

Populate a column via multi-dimensional lookup in SQL?

I have the following two tables:

TABLE_A:

Date USD EUR
2020-01-31 1.11 0.89
2020-02-28 1.15 0.93

TABLE_B:

Date Currency Amount
2020-01-31 USD NULL
2020-02-29 EUR NULL

I want to populate the Amount field in TABLE_B with the corresponding amount in TABLE_A dependent on the matching date and currency. Is there a way to perform this multi-dimensional lookup in SQL?

Upvotes: 0

Views: 66

Answers (1)

Ryan Wilson
Ryan Wilson

Reputation: 10765

The only join I can see that is common to both tables is the date value, you can join on that and then use a CASE expression to choose the appropriate value from tableA based on the value in tableB

SELECT CASE WHEN b.Currency = 'USD' THEN a.USD ELSE a.EUR END as [Amount]
FROM tableA AS a
INNER JOIN tableB AS b
ON b.Date = a.Date

Upvotes: 1

Related Questions