We help IT Professionals succeed at work.

How do you select this in MySQL in one query?

davideo7
davideo7 asked
on
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.
Comment
Watch Question

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.userID

Open in new window

Senior Developer
CERTIFIED EXPERT
Commented:
Or you can do something like this:

SELECT (SELECT userName FROM Users WHERE Users.userID=Message.messageFromUserID) MESSAGEFROMUSERIDNAME, (SELECT userName FROM Users WHERE Users.userID=Message.messageToUserID) MESSAGETOUSERIDNAME FROM Message
Sudaraka WijesingheWeb Application Programmer

Commented:
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.
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
sudaraka: You have a point there but I guess answer was already given.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.