CODEWITHSUNDEEP
mysql
Abhishek Kumar
Abhishek Kumar

Reputation: 2286

mysql query to display messages with sender and reciever

my messaging schema is like this 

USER  
id  
name 



MESSAGE  
id  
messagetitle    
sender_id    
receiver_id

I want to display messagetitle with sender and receivers name

for example MySQL query should display : 

+--------------+------------+--------------+
| MessageTitle | SenderName | Recievername |
+--------------+------------+--------------+
| Important    | Raj        | Vijay        |
| Solution     | Vijay      | Raj          |
+--------------+------------+--------------+

Upvotes: 2

Views: 1188

Answers (2)

Conrad Lotz
Conrad Lotz

Reputation: 8838

Try the following sql for your solution:

SELECT 
    m.messagetitle AS MessageTitle
    ,u1.Name AS SenderName
    ,u2.Name AS Recievername 
FROM MESSAGE m 
INNER JOIN USER u1 ON u1.id = m.sender_id 
INNER JOIN USER u2 ON u2.id = m.receiver_id

SQL FIDDLE DEMO

Upvotes: 3

Joachim Isaksson
Joachim Isaksson

Reputation: 181087

Should be a plain old simple join, freehand something like;

SELECT m.messagetitle, s.name sender, r.name receiver
FROM message m
JOIN user s ON m.sender_id=s.id
JOIN user r ON m.receiver_id=r.id

Upvotes: 1

Related Questions