CODEWITHSUNDEEP
mysqlsqljoin
Matt
Matt

Reputation: 1149

MySQL chat table, join user names?

I have a simple chat database like this:

id | from | to | message
---|------|----|-----------------
1  | 1    | 2  | hello
2  | 1    | 2  | are you there?
3  | 2    | 1  | yes I'm here!

Also there is a second table that holds the names 

`users`.`firstname` and `users`.`lastname`

I'm trying to combine the two, so when retrieving the chats, I'd also have the names of both participants. My query looks like this right now:

SELECT 
  `messages`.*, 
  CONCAT(`users`.`firstname`, " ", `users`.`lastname`) as `nameFrom`     
FROM `messages`     
  INNER JOIN 
     `users` ON `messages`.`from` = `users`.`id`

It works fine, but if I try to add another join, just change messages.from to messages.to I get errors.

How do I combine the first name & last name of each participant when retrieving the chat log?

Thanks

Upvotes: 1

Views: 1055

Answers (2)

John Woo
John Woo

Reputation: 263803

You need to basically join the table users twice on table messages provided that you supply an ALIAS to uniquely identify the tables with the same table name.

SELECT  messages.*, 
        CONCAT(a.firstname, ' ', a.lastname) as nameFrom,
        CONCAT(b.firstname, ' ', b.lastname) as nameTo  
FROM    messages     
        INNER JOIN users a
            ON messages.from = a.id
        INNER JOIN users b
            ON messages.to = b.id

Upvotes: 3

sgeddes
sgeddes

Reputation: 62851

Looks like you're trying to get both the sender and receiver? If so, this should work joining the Users table twice:

SELECT 
  `messages`.*, 
  CONCAT(u.`firstname`, " ", u.`lastname`) as `nameFrom`,
  CONCAT(u2.`firstname`, " ", u2.`lastname`) as `nameTo`
FROM `messages`     
  INNER JOIN 
     `users` u ON `messages`.`from` = u.`id`
  INNER JOIN 
     `users` u2 ON `messages`.`to` = u2.`id`

Upvotes: 3

Related Questions