How do you select this in MySQL in one query?
What I am trying to do is pull rows from one table but I need data selected from another table as it goes through each result.
Message Table:
--------------------------
messageFromUserID
messageToUserID
Users Table:
------------------
userID
userName
Objective: Selecting 2 different usernames for each result from the users table based on the message tables ids: messageFromUserID and messageToUserID.
So as each result is being pulled I want it to show the usernames with the data. This needs to be done in one query that is what I am trying to do.
Message Table:
--------------------------
messageFromUserID
messageToUserID
Users Table:
------------------
userID
userName
Objective: Selecting 2 different usernames for each result from the users table based on the message tables ids: messageFromUserID and messageToUserID.
So as each result is being pulled I want it to show the usernames with the data. This needs to be done in one query that is what I am trying to do.
VipulKadia
You can use LEFT JOIN.
SELECT
U1.userName as MessageFrom
,U2.userName as MessageTo
FROM
Message as M
INNER JOIN Users as U1 ON M.messageFromUserID=U1.userID
INNER JOIN Users as U2 ON M.messageToUserID=U2.userIDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Query suggested by Thomasian above is the way to get the result you need. However if you need to all rows from message table returned regardless of whether corresponding user Id exists in the users table, change those INNER JOINs to LEFT JOINs.
sudaraka: You have a point there but I guess answer was already given.