I have two tables: Accounts and Transactions. I would like to join them to have an output table as per below:

Accounts Table:

Transactions Table:

Output:

CodePudding user response：

You need to JOIN with Accounts table two times to get your expected result.

The query will be:

SELECT TR.TransactionID, 
       A1.AccounNumber AS FromAccount,
       A2.AccounNumber AS ToAccount,
FROM Transactions TR
INNER JOIN Accounts A1 ON A1.AccountID = TR.FromAccount
INNER JOIN Accounts A2 ON A2.AccountID = TR.ToAccount