How can I join two columns that both reference the same column in another table (Postgres SQL)?-CodePudding

Example

Accounts Table:

id | name
1  | Checking
2  | Visa

Transactions Table:

date | description | amount | from_id | to_id
10-8 | payment     | $100   | 1       | 2

Question:

How can I query the Transactions table and get the name for the from_id and to_id columns which both reference the id column in the Accounts table?

Using the example above, I'm trying to return:

date | description | amount | from     | to
10-8 | payment     | $100   | Checking | Visa

CodePudding user response：

you have to join it twice:

select t.date, t.description, t.amount, a_from.name fromname, a_to.name toName
from transactions t
join accounts a_to on t.to_id = a_to.id
join accounts a_from on t.from_id = a_from.id