Join postgres table that has two common columns of another table

Question

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?

JRA · Accepted Answer · 2021-12-15 15:34:08Z

3

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

answered Dec 15, 2021 at 15:34

JRA

4876 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

krown_loki Over a year ago

Oh, I didn't even think about this possibility. But how will a lot of joins affect a table with millions of records?

Collectives™ on Stack Overflow

Join postgres table that has two common columns of another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related