Match two ids from the table to a different table PgSQL

Question

So, here I have a exchange_rate table with fromcurr_id and tocurr_id and the names of the currency according to the id's are in another table currency.

exchng_rate_id | fromcurr_id | tocurr_id | exchange_rate 
----------------+-------------+-----------+---------------
              1 |           1 |         2 |  5.0000000000
              2 |           1 |         3 |  3.0000000000
              3 |           1 |         4 |  6.0000000000
              4 |           1 |         5 |  2.0000000000
              5 |           2 |         3 |  5.0000000000
              6 |           2 |         4 |  7.0000000000
              7 |           2 |         5 |  3.0000000000
              8 |           3 |         4 |  1.0000000000
              9 |           3 |         5 |  4.0000000000
             10 |           4 |         5 |  2.0000000000

and here's the description for the currency table:

Column  |          Type          | Collation | Nullable |               Default                
---------+------------------------+-----------+----------+--------------------------------------
 id      | integer                |           | not null | nextval('currency_id_seq'::regclass)
 curr_id | integer                |           | not null | 
 name    | character varying(255) |           |          | 

I need to show what id belongs to what currency name.(ie., fromcurr_id = name and tocurr_id = name)

This is what i tried...

Query1: Tried inner join on the tables, but the result yields only the names of the fromcurr_id.

select name, fromcurr_id, tocurr_id, name, exchange_rate 
from exchange_rate 
  inner join currency on exchange_rate.fromcurr_id = currency.curr_id;

Result:

      name      | fromcurr_id | tocurr_id |      name      | exchange_rate 
----------------+-------------+-----------+----------------+---------------
 ************** |           1 |         2 | ************** |  5.0000000000
 ************** |           1 |         3 | ************** |  3.0000000000
 ************** |           1 |         4 | ************** |  6.0000000000
 ************** |           1 |         5 | ************** |  2.0000000000
 *************  |           2 |         3 | ************   |  5.0000000000
 ************   |           2 |         4 |**************  |  7.0000000000
 *************  |           2 |         5 | ************   |  3.0000000000
 ************** |           3 |         4 | ************** |  1.0000000000
 ************** |           3 |         5 | ************** |  4.0000000000
 ************** |           4 |         5 | ************** |  2.0000000000
(10 rows)

The name in the first case is same as the second case, and not according to the currency table. (Had to censor the data for confidentiality)

---

Query2: Tried inner join with two conditions.

select name, fromcurr_id, tocurr_id, name, exchange_rate 
from exchange_rate 
  inner join currency on exchange_rate.fromcurr_id = currency.curr_id 
                     and exchange_rate.tocurr_id = currency.curr_id;

And this shows no results.

Answer 1

You need two joins, one for each currency:

select er.*, cfrom.*, cto.*
from exchange_rate er inner join
     currency cfrom
     on er.fromcurr_id = cfrom.curr_id join
     current cto
     on er.tocurr_id = eto.curr_id