labu77 labu77 - 3 months ago 10
MySQL Question

Double select usernames in one query

I have three tables:

User_table
,
Message_table
and
Message_table_to
.

User_table fields:

user_id

username

Message_table fields:

message_id

author_id

message_text

message_time

Message_table_to with this fields fields:

message_id

author_id

user_id

I need everything from the
message_table
and the name
Username
from
author_id
and
user_id
. How is it possible to get both
usernames
from the
user_table
with one query?

Notes: I tried the following query:

$sql = "SELECT u.user_id, u.username, p.message_id, p.message_text, p.message_time, p.author_id, k.message_id, k.user_id
FROM User_table u, Message_table p, Message_table_to k
WHERE
k.message_id = p.message_id
AND p.author_id = u.user_id
ORDER BY k.message_id DESC
LIMIT 5
";


But I get only the username of the
p.author_id
and not the username from the
k.user_id

Answer

You need to join the user_table twice using alias in order to get user names for sender and recipients. I also changed the query to use explicit joins instead of where criteria:

SELECT u1.user_id, u1.username as sender, p.message_id, p.message_text,     p.message_time, p.author_id, k.message_id, k.user_id, u2.username as recipient 
FROM User_table u1
INNER JOIN Message_table p ON p.author_id = u1.user_id
INNER JOIN Message_table_to k ON k.message_id = p.message_id
INNER JOIN User_table u2 ON k.user_id=u2.user_id
ORDER BY k.message_id DESC 
LIMIT 5
Comments