Two foreign keys referring to one primary key on SQL SERVER query select items-CodePudding

I'd to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like:

| userID        |    name       |      email       |   balance  |
| ------------- |---------------|------------------|------------|
| 101           | alpha         | [email protected]   |   1000     |
| 102           | bravo         | [email protected]   |    500     |
| 103           | charlie       | [email protected] |   2000     |

And the transaction table should look like:

 transactionID |  from_user    |   to_user        | transfer_amount  |
| ------------- |---------------|------------------|------------------|
|   1           | 101           |       103        |   100            |
|   2           | 102           |       101        |   150            |
|   3           | 102           |       103        |   200            |

just i needed this result:

|    row        |  from_user    |   to_user        | transfer_amount  |
| ------------- |---------------|------------------|------------------|
|   1           | alpha         |       charlie    |   100            |
|   2           | bravo         |       alpha      |   150            |
|   3           | bravo         |       charlie    |   200            |

Could someone give hints to provide SQL Server code?

Select from_user, to_user, name, transfer_amount from transaction iner join users on trans.id==user.id;

CodePudding user response：

SELECT T.TRANSACTION_ID,T.FROM_USER,U_FROM.NAME,
T.TO_USER,U_TO.NAME,T.TRANSFER_AMOUNT
FROM TRANSACTIONS AS T
JOIN USERS AS U_FROM ON T.FROM_USER=U_FROM.USER_ID
JOIN USERS AS U_TO ON T.TO_USER=U_TO.USER_ID

Something like this, I guess