how do use inner join to join two usernames from a second table, once as a sender and then as a rece-CodePudding

I have three tables in sql.

transcation_table: id(pk), date_time, sender_wallet(fk), amount_sent, sender_updated_balance, receiver_wallet(fk), amount_received, receiver_updated_balance

wallet_table: id(pk), userid(fk), wallet, currency, balance

user_table: id(pk), uname

all 'id' fields are primary keys

wallet_table.userid is foreign key of user_table.id

transaction_table.sender_wallet is foreign key of wallet_table.id

transaction_table.receiver_wallet is foreign key of wallet_table.id

I am trying to ask the database to give me a new table where any of the records in transaction_table contain a sender_wallet and receiver_wallet for a particular user.

The new returned table should be like:

date_time, sender_uname, amount_sent, sender_updated_balance, receiver_uname, amount_received, receiver_updated_balance

sender_uname and receiver_uname are new columns to be created for the purpose of making a distinction between the sender and receiver username in the newly returned table.

A returned result would be something like:

2023-02-03 09:57:38, marvin381, 40.00, 360.00, hamarni242, 40.00, 440.00

I made some poor attempts at trying to receive the intended result.

I am unable to see how I can pull the uname from the wallet_table effectively twice and join to the new table.

I also am not getting close to creating the new columns 'sender_uname' and 'receiver_uname'.

I have managed to get the inner join to work getting the data but with only one uname, but not the uname twice under 'sender' and 'receiver'.

which is not even working or coming close to the result.

CodePudding user response：

select t.id,
       t.date_time,
       su.uname as sender_uname,
       t.amount_sent,
       t.sender_updated_balance,
       ru.uname as receiver_uname,
       t.amount_received,
       t.receiver_updated_balance
from transcation_table t
         inner join wallet_table sw on t.sender_wallet = sw.id
         inner join wallet_table rw on t.receiver_wallet = rw.id
         inner join user_table su on su.id = sw.userid
         inner join user_table ru on ru.id = rw.userid
where su.id = x or ru.id = x;

That x would be a parameter (or a constant) written depending on your database which you didn't specify in tags.

PS: You could also join wallet_table and user table once with a CTE but I didn't want to go that route without knowing your database.

Here is a DBFiddle sample