CODEWITHSUNDEEP
sqlhivehiveql
Bala
Bala

Reputation: 11274

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)

Upvotes: 0

Views: 7382

Answers (2)

sticky bit
sticky bit

Reputation: 37487

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;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need two JOIN again :

SELECT b.acctbalance, b.acurr, b.acctbalance / r1.rate as `AB to GBP`,
       b.interest, b.icurr, b.interest / r2.rate as `Ints to GBP`,
       b.dividends, b.dcurr, b.dividends / r3.rate as `Divnd to GBP`
FROM balances b LEFT JOIN 
     xrates r1           
     ON b.acurr = r1.curr LEFT JOIN
     xrates r2  
     ON b.icurr = r2.curr LEFT JOIN
     xrates r3 
     ON b.dcurr = r3.curr;

Upvotes: 3

Related Questions