CODEWITHSUNDEEP
mysql
donm
donm

Reputation: 67

Fetch multiple records from multiple database using single query

I have two tables as follows:-

Users (Table1)

id name email     passs
1  u1   uemail1   pass1
2  u2   uemail2   pass2
3  u3   uemail3   pass3
4  u4   uemail4   pass4

Messages (Table2)

mess_id sender receiver message_text  send_time
1        1     2        text1         2019-03-25 09:39:05
2        1     2        text2         2019-03-30 15:10:54  
3        1     3        text3         2019-03-30 15:11:59
4        1     4        text4         2019-03-30 15:12:48
5        1     4        text5         2019-03-30 15:13:53
6        4     1        text6         2019-04-09 09:26:53

The logged in user is u1 and i want to get the latest conversation which is the 6th one okay.

Now what i want is to find the latest conversation of u1 and with whom and then find the details from users table of other user with whom u1 had the latest conversation

I hope you got my question and it's clear to everyone.

How can i do all this is 1 query

The result will be the other user's detail like this in the above case the user will be u4

id name email     passs
4  u4   uemail4   pass4

So far i have tried this but not getting the result what i wanted

select name from users where id=(select receiver_id,sender_id from messages where receiver_id or sender_id=1 order by send_time desc limit 1)

Upvotes: 0

Views: 212

Answers (2)

Nick
Nick

Reputation: 147206

Here's a somewhat complex but very general way of solving the problem. It finds the maximum send/receive time for each user in a derived table, then JOINs that to the messages table to find the other user related to that message, and then JOINs that again to the users table to find that user's details. The user of interest is specified in the WHERE clause at the end of the query.

SELECT u2.*
FROM users u1
JOIN (SELECT user, MAX(send_time) AS send_time
      FROM (SELECT sender AS user, MAX(send_time) AS send_time
            FROM messages
            GROUP BY sender
            UNION
            SELECT receiver, MAX(send_time)
            FROM messages
            GROUP BY receiver) mt
      GROUP BY user) mt ON mt.user = u1.id
LEFT JOIN messages m1 ON m1.sender = mt.user AND m1.send_time = mt.send_time
LEFT JOIN messages m2 ON m2.receiver = mt.user AND m2.send_time = mt.send_time
JOIN users u2 ON u2.id = COALESCE(m1.receiver, m2.sender)
WHERE u1.id = 1

Output:

id  name    email   passs
4   u4      uemail4 pass4

Demo on dbfiddle

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

One way is to determine the other user id in a Derived Table, and then join this to the user table, to get the user details.

SELECT 
  u.* 
FROM 
  user AS u 
JOIN 
(
  SELECT
    IF(sender <> 1, sender, receiver) AS other_user  /*Logged-in User Id is mentioned here */
  FROM messages 
  WHERE sender = 1 OR receiver = 1  /*Logged-in User Id is mentioned here */
  ORDER BY send_time DESC LIMIT 1   /*Get the latest message row */
) AS dt ON dt.other_user = u.id

IF(sender <> 1, sender, receiver) determines the other user id. If sender is not 1 (logged-in user), then we consider sender as the other user, else receiver. This is based on a (mostly valid) assumption that the sender and receiver will always be different values.

Upvotes: 1

Related Questions