Postgres - How to join on two columns?

Question

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

JNevill · Accepted Answer · 2022-05-02 21:05:34Z

2

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

edited May 2, 2022 at 21:05

answered May 2, 2022 at 20:55

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

3 Comments

Eulavo Over a year ago

But how do I keep the other results, where from_acc_id is null, please ?

JNevill Over a year ago

I missed that requirement. Change those to LEFT OUTER JOIN. I will update.

Eulavo Over a year ago

that's exactly what i needed. Thank you!

Thorsten Kettner · Accepted Answer · 2022-05-02 21:07:46Z

0

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

edited May 2, 2022 at 21:07

answered May 2, 2022 at 20:58

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

2 Comments

Eulavo Over a year ago

But how do I keep the other results, where from_acc_id is null, please ?

Thorsten Kettner Over a year ago

Oops, I missed that. You need outer joins for this, as JNevill has already shown. Sorry.

Collectives™ on Stack Overflow

Postgres - How to join on two columns?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related