Join postgres table that has two common columns of another table-CodePudding

I have two tables:

Transactions:
 --------- -------------- ------------ ----------- 
| id      | address_from | address_to | value     |
 --------- -------------- ------------ ----------- 
| 1       |        1     |     2      | 1000      |
| 2       |        1     |     2      | 500       |
 --------- -------------- ------------ -----------

Addresses:
 --------- -------------- 
| id      | address      |
 --------- -------------- 
| 1       |   address1   |
| 2       |   address2   |
 --------- --------------

I need to get all transactions with adresses instead id:

SELECT * FROM transactions tr
JOIN addresses ad ON tr.address_from = ad.id OR tr.address_to = ad.id
WHERE tr.address_from = 1 OR tr.address_to = 1

And then i get following result:

 --------- -------------- ----------- 
| id      | address      | value     |
 --------- -------------- ----------- 
| 1       |  address1    |   1000    |
| 1       |  address1    |   1000    | 
| 2       |  address2    |   500     |
| 2       |  address2    |   500     |
 --------- -------------- -----------

But i need something like this:

 --------- -------------- ------------- ----------- 
| id      | address_from | address_to  | value     |
 --------- -------------- ------------- ----------- 
| 1       |   address1   |   address2  | 1000      |
| 2       |   address1   |   address2  | 500       |
 --------- -------------- ------------- -----------

How can I get that result?
And isn't it so expensive to do join on two columns?

CodePudding user response：

You could try with 2 join on same table to get the expected result

SELECT tr.id,adf.address as address_from,adt.address as address_to,tr.value
    FROM transactions tr
JOIN addresses adf ON tr.address_from = adf.id
JOIN addresses adt ON tr.address_to = adt.id