CODEWITHSUNDEEP
mysqljoinmax
TRicks
TRicks

Reputation: 35

MySQL join 2 tables and retrieve the latest timestamp and message

I'm trying to recreate a conversation list like whatsapp where it displays the contact name of the person you are having a conversation with and also the time the message was posted and also the last message regardless of if it was from you or the contact that posted the message, In other words I need to always see the contact name and the last time the message was posted and the last message which could have been sent by me or the recipient, I also need to bring back the image of the other user from the users table.

USERS TABLE

userid |  first_name  |       url          |
 ---------------------------------------------
 101   |    name1     | www.image_url1.jpg |
 102   |    name2     | www.image_url2.jpg |
 103   |    name3     | www.image_url3.jpg |
 104   |    name4     | www.image_url4.jpg |

MESSAGES TABLE

MessageId  |  userid  | senderid | message  | timestamp |
-----------------------------------------------------------
    1      |    101   |    102   | message1 |  1234567  |
    2      |    102   |    101   | message2 |  1234578  |
    3      |    101   |    102   | message3 |  1235679  |
    4      |    104   |    101   | message4 |  1256379  |

What i'm trying to query from the database if my userid = 101 is the last message and timestamp from either me or the contact along with the contacts userid, first_name and url

  userid  |  first_name |        url         |  message  |  timestamp |
  ---------------------------------------------------------------------
    102   |    name2    | www.image_url2.jpg |  message3 |   1235679  |
    104   |    name4    | www.image_url4.jpg |  message4 |   1227878  |

1) I want to extract the userid, first_name and url of the contact from the users table
2) And extract the newest message and timestamp from the messages table from the contact

So in other words i need to show the last message and timestamp posted by either me or the other contact from the messages table and the url and username from the users table but not my username and image url just the contacts.

Here is the query i have so far

SELECT DISTINCT users.userid, users.first_name, users.url, message, MAX(messages.timestamp) AS utime FROM messages JOIN users ON users.userid = messages.senderid WHERE users.userid <> '101' AND (messages.userid = '101' OR messages.senderid = '101') GROUP BY 1,2,3 ORDER BY utime DESC

i also tried the below which didn't quite work

SELECT DISTINCT mems.userid, mems.first_name, mems.url, message, MAX(messaging.timestamp) AS utime 
FROM messaging 
JOIN mems 
ON mems.userid = CASE 
                 WHEN 
                      messaging.senderid = '101' 
                   THEN 
                      messaging.userid 
                   ELSE 
                      messaging.senderid 
                 END 
WHERE mems.userid <> '101' AND (messaging.userid = '101' OR messaging.senderid = '101') 
GROUP BY 1,2,3 ORDER BY utime DESC

Upvotes: 0

Views: 399

Answers (2)

jussius
jussius

Reputation: 3274

Something like this should work.

SELECT IF(m.userid = '101', m.senderid, m.userid) contact, m.message, m.timestamp, u.first_name, u.url
FROM messages m
INNER JOIN(
    SELECT MAX(timestamp) max_time, IF(userid = '101', senderid, userid) contact 
    FROM messages 
    WHERE userid = '101' OR senderid = '101'
    GROUP BY contact
) j ON j.contact = IF(m.userid = '101', m.senderid, m.userid) AND m.timestamp = j.max_time
LEFT JOIN users u ON u.userid = IF(m.userid = '101', m.senderid, m.userid)

P.S. this doesn't actually tell you who sent the latest messages (you or your contact) it might be more useful to get both the senderid and userid for every result row, instead of just the contact id.

Upvotes: 1

St3an
St3an

Reputation: 806

try something like :

select * from (
 select m.senderId userid,
        u.first_name,
        u.url,
        m.message,
        m.timestamp        
 from users u inner join messages m
              on u.userid=m.userid
 order by timestamp desc
) t
limit 1;

Upvotes: 0

Related Questions