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 | alpha@mail.com | 1000 | | 102 | bravo | bravo@mail.com | 500 | | 103 | charlie | charlie@mail.com | 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;
Advertisement
Answer
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