MESSAGE TABLE USERS
msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name
1 1 2 hi 22/1/14 2 jeff
2 2 1 hello 22/1/14 3 john
3 1 2 howdy 22/1/14 4 james
4 2 1 nothing 22/1/14
5 3 1 yes 22/1/14
6 1 3 maybe 22/1/14
7 4 1 no 22/1/14
8 1 4 cool 22/1/14
9 4 1 hot 22/1/14
*note that user 1 does not have any data in the users table.
I am having an issue getting all the messages uniquely in a stream based on who it was sent from. That is I want the last message that is exchanged between each user who communicates with user 1
. I am trying to populate a list that should should the last message exchanged, so the list would be similar to this
MESSAGE LISTING
name | last_message
jeff nothing
john maybe
james hot
Last messages exchanged between user 1 and the other users at all times, user 1 does not have data saved in the users table but I have access to his user_id
if needed in query.
I tried doing a JOIN
with the users table ON msg_from = user_id
but this only returns the last message from one user, so the last message exchanged between user 1 and user 2 is howdy
instead of nothing based on this sample. I am not quite sure how to tune this query to represent the correct dataset that is needed, any help would be appreciated thanks.
Best Answer
The subquery computes, for each user in
USERS
, the latestmsg_id
value: