I've been able to select all conversations with a message attached to them, but I've not been able to make sure it's the latest message. Please explain why the query doesn't work. I am trying to avoid using a subquery or joining the tables against themselves.
DESC ir_chats;
+-----------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+-------------------+----------------+
| chat_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| creator_user_id | int(11) | NO | MUL | NULL | |
| friend_user_id | int(11) | NO | MUL | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
Desc ir_chat_messages;
+-------------+-----------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+-------------------+-------+
| chat_id | int(10) unsigned | NO | MUL | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
| read_status | enum('read','unread') | NO | | unread | |
| message | text | NO | | NULL | |
+-------------+-----------------------+------+-----+-------------------+-------+
SELECT * FROM ir_chats;
+---------+-----------------+----------------+---------------------+
| chat_id | creator_user_id | friend_user_id | created |
+---------+-----------------+----------------+---------------------+
| 1 | 100 | 200 | 2014-10-15 18:06:54 |
+---------+-----------------+----------------+---------------------+
1 row in set (0.00 sec)
SELECT * FROM ir_chat_messages;
+---------+---------+---------------------+-------------+--------------------------+
| chat_id | user_id | created | read_status | message |
+---------+---------+---------------------+-------------+--------------------------+
| 1 | 100 | 2014-10-15 18:16:06 | | This is a message. |
| 1 | 200 | 2014-10-15 18:26:59 | | This is another message. |
+---------+---------+---------------------+-------------+--------------------------+
2 rows in set (0.00 sec)
SELECT ir_chats.chat_id, ir_chat_messages.created, ir_chat_messages.message
FROM ir_chat_messages
JOIN ir_chats USING (chat_id)
GROUP BY ir_chats.chat_id
ORDER BY ir_chat_messages.created DESC;
+---------+---------------------+--------------------+
| chat_id | created | message |
+---------+---------------------+--------------------+
| 1 | 2014-10-15 18:16:06 | This is a message. |
+---------+---------------------+--------------------+
1 row in set (0.00 sec)
I asked if there was a way to do this without a subquery or joining another table. Are you saying it's not possible? The attached 'duplicate' post doesn't answer my question.