SQL - EXCEPTION TO A JOIN STATEMENT

Question

I have two tables I would like to join. One has currency references and the other has the names for the currency references. It is like a regular inner join exercise with one exception. The currency reference of 0 in the first table is actually 160 in the other. How can I manipulate my join statement to overcome this problem?

TABLE 1

       CREDIT TYPE               TRCURR
          CAR LOAN                    0
     CONSUMER LOAN                    0
          CAR LOAN                   20
          CAR LOAN                    1

TABLE 2

  CURRENCYREF     NAME_
            1       EUR
           20       YEN
          160       USD

Note that all TRCURR - CURRENCYREF combinations are correct except for the fact that the currency 160 is 0 in TABLE 1.

THANKS!

Answer 1

you can use multiple conditions in join statements like:

SELECT *
FROM table1 t1
inner join table2 t2 on t1.trCurr = t2.currencyRef
    or (t1.trCurr = 0 AND t2.currencyRef = 160)

Answer 2

you can use CASE, for example, firstly and then make an INNER JOIN statement So, it like :

 select * from (
 select case when tabl.TRCURR = 0 then  '160',
             when tabl.TRCURR = 1 the   '...' end as TRCURR, tabl.* 
 from table_1 tabl) t1
 inner join table_2 t2
 on t1.TRCURR = t2.CURRENCYREF     

something like that