mysql output message,私人聊天系统MYSQL查询显示发件人/接收者的最后一条消息

Here I am extending my previous question.

In addition, I created one more table called users from where I will get users info.

messages table

message_id|sender_id||receiver_id|message_text| created_time

1 101 102 Message A 2012-06-07 08:07:18

2 101 102 Message B 2012-06-07 08:10:20

3 103 102 Message C 2012-06-07 08:12:43

users table:

id | name

101 bob

102 jack

103 mark

Now finally I am able to get following results:

name|message_text | created_time

mark message C 2012-06-07 08:12:43

bob message B 2012-06-07 08:10:20

By using this query:

SELECT * FROM messages,users

WHERE messages.receiver_id = 102

AND messages.sender_id=users.id

AND messages.created_time IN

(SELECT MAX(created_time)

FROM messages

GROUP BY sender_id)

ORDER BY messages.created_time DESC

Now what I want in my result is

if jack(id:102) reply to mark(id:103) then how can I get this output:

name|message_text | created_time

mark message D 2012-06-07 08:12:48

bob message B 2012-06-07 08:10:20

NOTE:HERE "MESSAGE D" AND TIME STAMP IS OF JACK WHICH HE REPLIED TO MARK.

where message_text field will display the last message between mark and jack

created_time field will show message created time

and name field will show the name of person to whom jack is sending or receiving messages.

I am thinking we need to modify/split tables but don't know how and what query will do this task.

解决方案

If this is a group chat don't limit to a receiver_id like messages.receiver_id = 102

SELECT users.name,

temp.MESSAGE_TEXT,

temp.created_time

FROM

(SELECT sender_id, MESSAGE_TEXT, created_time

FROM messages

WHERE created_time IN

(SELECT MAX(created_time)

FROM messages

GROUP BY sender_id)) AS temp

LEFT JOIN users ON temp.sender_id = users.id;

show last message each sender sent