Clever Geek Handbook

SQL - JOIN EXCEPTION

I have two tables that I would like to join. One has currency references and the other has names for currency references. It looks like a regular pooling exercise with one exception. The currency peg 0 in the first table is actually 160 in the other. How can I manipulate my join expression to solve 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 combinations TRCURR

are CURRENCYREF

correct, except for the fact that the currency 160

is equal 0

in TABLE 1.

THANK!

+3


source to share


3 answers


you can use multiple conditions in join operations, for example:



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


source


I don't know if I understood your requirements correctly or not, but according to my understanding, you can try below query for your exception case as it will overcome the 0 = 160 mismatch barrier.

SELECT * 
FROM TABLE1 t1
INNER JOIN TABLE1  t2 ON t1.TRCURR = t2.CURRENCYREF OR (t1.TRCURR = 0 AND t2.CURRENCYREF = 160)


thank

+2


source


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

 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

+1


source






More articles:


All Articles