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)
Advertisement
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 NULL
s 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;