0

I have two tables for storing exchange rates in my system. the first one agent_rate is as follows. 

ID  Currency Rate
=======================
3   GBP      0.65
4   EUR      0.70
5   JPY      57.4

the second table exchange_rate is as follows.

ID  Currency Rate
=======================
1   USD         1
2   ZMK       200
3   GBP       0.5
4   EUR      0.75
5   JPY      60.4
6   CHF       0.9

I want to select all the rows in the first table agent_rate and then add all the missing values of ID from the exchange_rate table. I wanted to use a union statement with distinct on a single column but I failed to. My current solution as follows (visual studio)

  1. select from agent_rate table and fill rate datatable
  2. set unique field i.e. ID in rate datatable
  3. select and fill from exchange_rate table into a temp datatable
  4. move records from temp datatable to rate datatable and ignore errors

the resulting table is(should be) as follows:

ID  Currency Rate
=======================
1   USD         1
2   ZMK       200
3   GBP      0.65
4   EUR      0.70
5   JPY      57.4
6   CHF       0.9

Is there a better way to do this in Sql?

Vectoria
  • 1,627
  • 14
  • 13

1 Answers1

1

You can join the table and select the agent values first if they exist, otherwise the vaolue from the exchange table

select coalesce(a.id, e.id) as id, 
       coalesce(a.currency, e.currency) as currency,
       coalesce(a.rate, e.rate) as rate
from exchange_rate e
left agent_rate a on a.id = e.id

coalesce returns the first non-null value in the list provided.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • But if an a-row exists and it's rate column is NULL, should NULL or e.rate be returned? – jarlh Feb 23 '15 at 10:14
  • Who says there is such a null column? Does not look like it in the example. Why always make things difficult if they don't have to be? – juergen d Feb 23 '15 at 10:16
  • It was more of a question to the OP, to consider table definition consequences. (Like when applications with NOT IN sub-query suddenly doesn't work anymore, just because a first NULL value is there...) – jarlh Feb 23 '15 at 10:21
  • Awesome! this is helpful! Accorrding to http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php . the COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values. – Vectoria Feb 24 '15 at 11:30