How to join single table with multiple columns in Hive?

I have 2 tables: one has exchange rates (to GBP), the other has various amount columns with different currencies. I am joining these two tables and display all amount values in GBP, but I get nulls for many.

> select * from xrates;
+--------------+--------------+--+
| xrates.curr  | xrates.rate  |
+--------------+--------------+--+
| GBP          | 1            |
| INR          | 89.74        |
| EUR          | 1.23         |
+--------------+--------------+--+

> select * from balances;
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+
| balances.acctbalance  | balances.acurr  | balances.interest  | balances.icurr  | balances.dividends  | balances.dcurr  |
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+
| 334.23                | GBP             | 1.2                | GBP             | 0                   | GBP             |
| 10000                 | INR             | 100                | EUR             | NULL                | GBP             |
+-----------------------+-----------------+--------------------+-----------------+---------------------+-----------------+--+

Here is the query joining these two tables

SELECT 
  acctbalance, acurr, acctbalance/rate as `AB to GBP`,
  interest, icurr, interest/rate as `Ints to GBP`,
  dividends, dcurr, dividends/rate as `Divnd to GBP`
FROM    
   balances           --table 1
LEFT JOIN 
   xrates             --table 2
  on acurr = curr     --account balance currency
 and icurr = curr     --interest currency
 and dcurr = curr     --dividend currency

+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
| acctbalance  | acurr  | ab to gbp  | interest  | icurr  | ints to gbp  | dividends  | dcurr  | divnd to gbp  |
+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
| 334.23       | GBP    | 334.23     | 1.2       | GBP    | 1.2          | 0          | GBP    | 0             |
| 10000        | INR    | NULL       | 100       | EUR    | NULL         | NULL       | GBP    | NULL          |
+--------------+--------+------------+-----------+--------+--------------+------------+--------+---------------+--+
(2nd row - converted to GBP columns - has nulls)

Answer

Since the currencies of the three different amounts can all vary, you cannot join the exchange rates just once. This will only find an exchange rate record, if the currencies are all the same. So you get NULLs where the currencies differ as no exchange rate was found. Try to join it three times separately.

SELECT b.acctbalance,
       b.acurr,
       b.acctbalance / xa.rate `AB to GBP`,
       b.interest,
       b.icurr,
       b.interest / xi.rate `Ints to GBP`,
       b.dividends,
       b.dcurr,
       b.dividends / xd.rate `Divnd to GBP`
       FROM balances b
            LEFT JOIN xrates xa
                      ON xa.curr = b.acurr
            LEFT JOIN xrates xi
                      ON xi.curr = b.icurr
            LEFT JOIN xrates xd
                      ON xd.curr = b.dcurr;