MySQL Select Statement - For Messages

Answer 1

Try

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent,  pm_data.title, pm_data.thread_id, pm_data.id as dataid
FROM pm_data, pm_info, tblUsers as sender 
WHERE pm_info.message_id = pm_data.id 
  AND pm_data.sender_id = sender.usrID
  AND pm_info.receiver_id = '$usrID'
GROUP BY thread_id
ORDER BY date_sent DESC
LIMIT 0,1

Answer 2

Try

SELECT pm_info.is_read, group_concat(DISTINCT sender.usrFirst) as sender_name,
    pm_data.date_sent, pm_data.title, pm_data.thread_id, pm_data.id as dataid,
    MAX(date_sent) AS thread_max_date_sent
FROM pm_info
INNER JOIN pm_data ON pm_info.message_id = pm_data.id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID
WHERE pm_info.receiver_id = '$usrID'
GROUP BY pm_data.thread_id
ORDER BY thread_max_date_sent DESC;

Feel free to disagree with the group_concat part, if it doesn't meet your needs; it just lists all of the relevant senders instead of picking one arbitrarily.

Answer 3

It actually seems like the query in the original question only requires a very small change for getting the actual most recent record for each thread.

1. drop the sender_id in the subquery's GROUP BY fields
2. drop the *pm_data.sender_id = deriv1.sender_id* in the ON clause

SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent,  pm_data.title, pm_data.thread_id, pm_data.id as dataid, thread_max_date_sent
FROM pm_info
INNER JOIN pm_data ON pm_info.message_id = pm_data.id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID
INNER JOIN (SELECT thread_id, /*sender_id,*/ MAX(date_sent) AS thread_max_date_sent 
       FROM pm_data GROUP BY thread_id /*, sender_id*/) deriv1 
    ON pm_data.thread_id = deriv1.thread_id AND pm_data.date_sent = deriv1.thread_max_date_sent /*AND pm_data.sender_id = deriv1.sender_id*/
WHERE pm_info.receiver_id = '$usrID'
ORDER BY deriv1.thread_max_date_sent DESC

As a side note: if possible, look for the max messageID in that subquery instead of max(date_sent)

Answer 4

My suggestion would be to split your database into two tables for better normalization: "thread" and "message". There is thread information that would be common to all messages, like the thread title. You're wasting space in the message table by repeating the same values.

You can have a "last post time" field the the thread table, which is updated on each new post. Then it's only a simple matter of selecting from the thread table and ordering by the last post. It will be a lot faster too.