How to combine two different SQL queries in one SQL statement?

Question

I have two tables which are:

Members    
Member_id | Member_user_name | Member_account_id

and

Members_transaction
Member_id (sender) | Member_transaction_id | Member_account_id (recipient) | Amount | from_to

All columns have data but from_to is a new column added. I would like to add the username of sender or recipient into the column from_to.

I had use below SQL query to find out the sender's or recipient's username:

select member_user_name member_involved 
  from Members_transaction 
  left 
  join members 
    on members_transaction.member_account_id = members.member_account_id

I want to add the SQL query in this SQL query:

SELECT member_transaction_id
     , mp.member_account_id
     , m.member_user_name
     , amount
     , CASE 
       -- when the amount starts with "-" such as "-100" it means paid to recipient 
       WHEN amount LIKE '-%' THEN 'Paid to' + member_involved 
       -- else it is  received from the sender
       ELSE ' Received from '+ member_involved
     END AS from_to
    FROM members_transaction MP (nolock)  
    LEFT 
    JOIN members M (NOLOCK) 
      ON M.MEMBER_ID = MP.MEMBER_ID 
     AND M.IS_USE = 1 
   WHERE MP.IS_USE = 1

Since the condition is different (the ON), how can i combine the SQL query of finding sender or recipient into the below SQL query?

Answer 1

You can join twice with members, once for the sender name, once for the receiver name:

SELECT member_transaction_id
 , mp.member_account_id
 , m.member_user_name
 , amount
 , CASE 
     -- when the amount starts with "-" such as "-100" it means paid to recipient 
     WHEN amount LIKE '-%' THEN 'Paid to' + M2.member_user_name 
     -- else it is  received from the sender
     ELSE ' Received from '+ M.member_user_name
   END AS from_to
 FROM members_transaction MP (nolock)  
 LEFT JOIN members M  (NOLOCK) ON M.MEMBER_ID = MP.MEMBER_ID AND M.IS_USE = 1 
 LEFT JOIN members M2 (NOLOCK) ON M2.member_account_id = MP.member_account_id AND M2.IS_USE = 1 
WHERE MP.IS_USE = 1