join to non-unique field without subquery-CodePudding

I have a master table that has among other fields, a currency symbol

master:

 ---- ------ -------- -------- 
| id | data | values | symbol |
 ---- ------ -------- -------- 
| 1  | asdf | asdf   | US$    |
 ---- ------ -------- -------- 
| 2  | asdf | asdf   | U$     |
 ---- ------ -------- -------- 
| 3  | asdf | asdf   | C$     |
 ---- ------ -------- -------- 
| 4  | asdf | asdf   | MP     |
 ---- ------ -------- --------

and I want to join it with a currency table to get the ISO value for it, however I don't have the id in the first table, but the "symbol" which in the second table is not unique.

currency:

 ---- --------- ------ -------- ------ 
| id | country | iso  | symbol | desc |
 ---- --------- ------ -------- ------ 
| 1  | US      | USD  | US$    | asdf |
 ---- --------- ------ -------- ------ 
| 2  | US      | USD  | U$     | asdf |
 ---- --------- ------ -------- ------ 
| 3  | US      | USD  | U$     | XYZ  |
 ---- --------- ------ -------- ------ 
| 4  | CA      | asdf | C$     | asdf |
 ---- --------- ------ -------- ------ 
| 5  | MX      | asdf | MP     | asdf |
 ---- --------- ------ -------- ------

How do I join so I can get the corresponding ISO column for it? right now I'm doing the following:

select m.id, 
       m.data, 
       m.values, 
       m.symbol, 
       c.iso 
       from master m
       inner join (select distinct symbol, iso from currency) c on c.symbol = m.symbol

However although this works how should I do it without the "distinct" or even better without a subquery as I feel this is not performing in the long run.

CodePudding user response：

If you are using subquery best you can use CTE (common table expression)

With cte as
(
select distinct symbol as symbol, iso from currency
)
   select m.id, 
           m.data, 
           m.values, 
           m.symbol, 
           c.iso 
           from master m
           inner join cte c on c.symbol = m.symbol

CodePudding user response：

Since you are only after the iso column here you could use a simple subquery, such as

select m.id, 
       m.data, 
       m.values, 
       m.symbol, 
       (select top(1) iso from currency c where c.symbol = m.symbol) iso
       from master m;

This would probably be more efficient as it would avoid a distinct-sort, which is unecessary since all rows are identical; as soon as the top operator returns a row it's done and moves on.

Of course at a glance this looks like a code smell but is really because your table is not normalised.