mySQL Return value from 'users' table in 'messages' table where three columns would return a different user

Question

I've been searching and tried several attempts to get this right. I've been through INNER JOIN, LEFT JOIN, SELECT and I'm just not making progress.

I have a messages table with columns id, from_user_id, to_user_id, time, actioned_by, date, message. In my users table there is id, first_name, last_name plus a few other things like email, password, permissions etc.

The columns from messages table from_user_id, to_user_id and actioned_by are INTs which would correspond to the id in users table and I would like to return the users first_name and last_name to a new table. So if my users table looked like:

id: 1, first_name: Dave, last_name: Someone
id: 2, first_name: John, last_name: Someoneelse
id: 3, first_name: Admin, last_name: User

And my messages table looked like:

id: 1, from_user_id:1, to_user_id:2, time:1425818720, actioned_by: 3, date:1425772800, message:Here is a good message

Then I'd return:

id: 1, from_user_id: Dave Someone, to_user_id:John Someoneelse, time:1425818720, actioned_by: Admin User, date:1425772800, message:Here is a good message

So far my best attempt has been this

SELECT `message_history`.`id`,`message_history`.`from_user_id`, `message_history`.`to_user_id`,`message_history`.`actioned_by` `message_history`.`time`, `message_history`.`date`, `message_history`.`message` 
       (SELECT `users`.`first_name`, `users`.`last_name` 
       FROM `users` 
       WHERE  `users`.`id`=`message_history`.`from_user_id`
       ) AS `from`,
       (SELECT `users`.`first_name`, `users`.`last_name` 
       FROM `users` 
       WHERE `users`.`id`=`message_history`.`to_user_id`
       ) AS `to`,
       (SELECT `users`.`first_name`, `users`.`last_name` 
       FROM `users` 
       WHERE `users`.`id`=`message_history`.`actioned_by`
       ) AS `actioned` 
FROM `message_history`

Is this even possible in mySQL or would I be aswell writing a PHP function to get the names?

Many Thanks

Dave

dbinns66 dbinns66 · Accepted Answer · 2015-03-12T17:39:35

try something like this (I think I got all your fields...) this assumes the only id that may not be assigned is actioned_by... and the concat for each name will return them as "first last" so no need to combine them via php

    select 
        `message_history`.`id`,`message_history`.`from_user_id`, `message_history`.`to_user_id`,`message_history`.`actioned_by` `message_history`.`time`, `message_history`.`date`, `message_history`.`message`,
        concat(from_user.first_name, '', from_user.last_name) as `from`,
        concat(to_user.first_name, '', to_user.last_name) as `to`,
        ifnull(concat(actioned_user.first_name, '', actioned_user.last_name),'') as `actioned`
    from 
        message_history 
        join users from_user on 
            message_history.from_user_id = users.id
        join users to_user on
            message_history.to_user_id = users.id
        left join users actioned_user on
            message_history.actioned_by = users.id

mySQL Return value from 'users' table in 'messages' table where three columns would return a different user

2 Answers