Fetch multiple records from multiple database using single query

Question

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)

Add the expected output in tabular format to question. In the same way, as you have specified the table structure(s). — Madhur Bhaiya
– Madhur Bhaiya, Commented Apr 9, 2019 at 13:48
Please edit your question and include what you have tried that is not producing the result you need. — Dave
– Dave, Commented Apr 9, 2019 at 13:50
@MadhurBhaiya please check the question again i have added the result which i wanted — donm
– donm, Commented Apr 9, 2019 at 13:52

Madhur Bhaiya · Accepted Answer · 2019-04-09 14:21:53Z

1

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.

answered Apr 9, 2019 at 14:21

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donm Over a year ago

Thanks a lot madhur i need the if condition on my query and then i get what i wanted

Nick · Accepted Answer · 2019-04-09 14:27:06Z

1

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

answered Apr 9, 2019 at 14:27

Nick

147k23 gold badges67 silver badges106 bronze badges

1 Comment

donm Over a year ago

Thanks nick for answering but i got the my solution from madhur's answer

Collectives™ on Stack Overflow

Fetch multiple records from multiple database using single query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related