SQLite – How to Join Tables with Group

join;sqlitesubquery

            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 latest msg_id value:

SELECT *
FROM MessageTable
WHERE msg_id IN (SELECT (SELECT msg_id
                         FROM MessageTable
                         WHERE msg_to   = Users.user_id
                            OR msg_from = Users.user_id
                         ORDER BY msg_id DESC    -- or by a timestamp
                         LIMIT 1)
                 FROM Users)