Geek asks and answers

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

I have two tables in my database

  • Table: userswith columns: id, username, emailetc.

  • Table: users_messageswith columns: id, to, from, messages,sent_time

The columns toand fromare the keys usersof users. The problem is what I need:
msgstr "select all users in and from username and message".

For example, if a table row looks like this:

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

and if 11 is john and 14 is a label, then I need the output as follows:

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

, . , ?

:

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

, . ?

+3
3
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
+4

   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

,

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


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

Yes, self-service can help you achieve the desired result:

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

0
source

More articles:


All Articles