Mysql queries two tables, retrieves messages for each conversation
I have two tables.
One of them, called "conversations", has the following data.
ConversationID Sender Reciever
1 bla1 bla2
2 bla1 bla3
3 bla1 bla4
Another called "Messages" has the following data.
MessageID MessageText TimeAddedMessage ConversationID
1 helo 2012-03-12 13:00:00 2
2 helo 2012-03-12 13:01:00 1
3 helo 2012-03-12 13:02:00 3
4 helo 2012-03-12 13:03:00 3
5 helo 2012-03-12 13:04:00 2
The result I want to get from the request is the following:
5 helo 2012-03-12 13:04:00 2
4 helo 2012-03-12 13:03:00 3
2 helo 2012-03-12 13:01:00 1
This means that we need the most recent comment for each conversation (DESC sorted by time).
Any help was appreciated.
source to share
Try -
SELECT m2.*, c.Sender
FROM (
SELECT m1.ConversationID, MAX(m1.MessageID) AS MessageID
FROM Messages m1
GROUP BY m1.ConversationID
) latest_msg
INNER JOIN Messages m2
ON latest_msg.MessageID = m2.MessageID
AND latest_msg.ConversationID = m2.ConversationID
INNER JOIN Conversations c
ON m2.ConversationID = c.ConversationID
ORDER BY m2.MessageID DESC
EDIT . I modified the above query to include the Sender value from the Conversations table. I noticed that your structure for speaking is a bit weird. The conversation goes from one user to another user, but there is no way to determine which user wrote each message. Is this intentional?
source to share
You can do this by creating a view with a dialog interface and max TimeAddedMessage and attaching it to messages:
select Messages.MessageID, Messages.MessageText,
Messages.TimeAddedMessage, Messages.ConversationID
from Messages inner join
(
select ConversationID, max (TimeAddedMessage) TimeAddedMessage
from Messages
group by ConversationID
) LastMessages
on Messages.ConversationID = LastMessages.ConversationID
and Messages.TimeAddedMessage = LastMessages.TimeAddedMessage
source to share
Simple query with good performance for relatively small datasets.
SELECT m1.*
FROM Messages m1 LEFT JOIN Messages m2
ON (m1.ConversationId = m2.ConversationId AND m1.TimeAddedMessage < m2.TimeAddedMessage)
WHERE m2.MessageID IS NULL;
Modified from this post .
This kind of counter is intuitive, but has become part of an overused cookbook recipe.
source to share