Postgres - How to join on two columns?-CodePudding

I have a table containing account information:

And another table, containing transactions information:

I'd like to retrieve both the titles from transactions.from_acc_id and transactions.to_acc_id

So far, I'm only able to retrieve either one or the other with the following JOIN:

SELECT transactions.transaction_type, 
transactions.from_acc_id, 
transactions.to_acc_id,
transactions.amount,
account.title AS "ACCOUNT DESTINATION"
FROM transactions
JOIN account 
ON transactions.to_acc_id = account.acc_id

This gives me the title of transactions.to_acc_id.

How can I add another field containing the title of transactions.from_acc_id with the same SELECT statement, please ?

Thanks

EDIT: I'd like to keep all field from the Select statement, adding the title of transactions.from_acc_id where is pertinent

CodePudding user response：

You join your account table in twice and give each instance its own alias. Furthermore, to ensure that every record from transactions table shows up and only those records from your accounts table (both source and destination), use a LEFT OUTER JOIN instead of your implicit INNER JOIN that you are currently using.

SELECT transactions.transaction_type, 
  transactions.from_acc_id, 
  transactions.to_acc_id,
  transactions.amount,
  dest.title AS "ACCOUNT DESTINATION",
  src.title AS "ACCOUNT SOURCE"
FROM transactions
  LEFT OUTER JOIN account as dest
    ON transactions.to_acc_id = dext.acc_id
  LEFT OUTER JOIN account as src
    ON transactions.from_acc_id = src.acc_id

For more information on types of joins that are available in most databases, check out W3Schools SQL Joins page

CodePudding user response：

Simply join the table twice. Use table aliases to tell the source account from the target account.

SELECT 
  t.transaction_type, 
  t.from_acc_id, 
  t.to_acc_id,
  t.amount,
  from_acc.title AS from_account,
  to_acc.title AS to_account
FROM transactions t
LEFT JOIN account from_acc ON from_acc.acc_id = t.from_acc_id
LEFT JOIN account to_acc ON to_acc.acc_id = t.to_acc_id