CODEWITHSUNDEEP
phpmysqlsql
M I
M I

Reputation: 3682

Mysql - how to get data for two foreign key columns of same table

I have two tables in my database

  1. Table: users with columns: id, username, email etcs

  2. Table: users_messages with columns: id, to, from, messages, sent_time

Columns to and from are keys users from users table. Issue is that I need to:
"select all users to and from username and message".

For instance if table row is like below:

From, To,   Message
11,   14,   Hi, How are you
14,   11,   Hello,

and if 11 is john and 14 is mark, then I need output like this:

From,   To,     Message
john,   mark,   Hi, How are you
mark,   john,   Hello,

With my query I can only get username for from column or to column but not for both. How can I perform a query that gives me from and to both usernames?

The following query gives me empty result:

SELECT  u.username, m.message, m.from, m.to  
FROM  `users_messages` m,  `users` u
where 
m.from = u.id and  m.to = u.id

LIMIT 0 , 30

I have tried other queries, but not able to get the desired output. Do I need a self join as well for messages table?

Upvotes: 3

Views: 2495

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

   select u.`name` as `FROM`, u2.`name` as `TO`, `Message` 
   from users_messages um 
   inner join users u On um.`From` = u.id
   inner join users u2 On um.`To` = u2.id
   LIMIT 0 , 30

DEMO HERE

if you want to get specified messages from one user add this

  WHERE u.`name` = 'mark' // to get messages FROM mark

or this

  WHERE u2.`name` = 'mark'  // to get messages TO mark

Upvotes: 4

user3260022
user3260022

Reputation: 69

Yes self join can help you achieve the desired output :

SELECT  u1.username, m.message, u2.username 
FROM  users_messages m,  users u1, users u2
where 
m.from = u1.id and  m.to = u2.id

LIMIT 0 , 30

Assuming : from and to will not have null values

Upvotes: 0

Jiří Chmiel
Jiří Chmiel

Reputation: 876

SELECT u1.username AS "sender", u2.username AS "recipient", m.message, m.from, m.to  
FROM `users_messages` m
LEFT JOIN `users` u1 ON (u1.id = m.from)
LEFT JOIN `users` u2 ON (u2.id = m.to)
WHERE m.from = XX OR m.to = XX

Upvotes: 5

Related Questions