SQL Query to return multiple data from inner join

I have two database table, one is registerd user table and the other one is chat message table. I want to list users that have conversion in chat message table. The below sql query does it but it show the active user name as on the list instead of the party user.

## Table users_account

reg_userid | Name
--------------------------
1          | Peter   
2          | John

Table chatroom_message

userid_a | userid_b
--------------------------
1          | 2   
3          | 1

SQL

SELECT * FROM chatroom_message cm
INNER JOIN users_account ua ON cm.userid_a = ua.reg_userid 
WHERE cm.userid_a = :chat_client
UNION ALL
SELECT * FROM chatroom_message cm
INNER JOIN users_account ua ON cm.userid_b = ua.reg_userid 
WHERE cm.userid_b = :chat_client

Current Session User Id = 1 (Peter), so when i run above sql query it return below table

Resault

reg_userid | Name
--------------------------
1          | Peter

My problem is how do i run an sql query to check if the active user is in chat message userid_a OR userid_b
If the result is found let it return the name of the chat party which is John, as his contact chat list, same goes to John When he login the result will also show him Peter not his own now

I want my result be John login to his account the name of Peter will show as his chat partner.

reg_userid | Name
--------------------------
1          | Peter

I want my result be Peter login to his account the name of John will show as his chat partner.

reg_userid | Name
--------------------------
2          | John

Solution

I guess you are almost there. This is probably what you are looking for:

SELECT ua.reg_userid, ua.name FROM chatroom_message cm
INNER JOIN users_account ua ON cm.userid_b = ua.reg_userid 
WHERE cm.userid_a = :chat_client
UNION ALL
SELECT ua.reg_userid, ua.name  FROM chatroom_message cm
INNER JOIN users_account ua ON cm.userid_a = ua.reg_userid 
WHERE cm.userid_b = :chat_client