SQL get most recent joined record

I’ve the following tables:

chat: chatId, jobId

chat_message: userId, chatId, message, created_at

chat_user: chatId, userId

Am trying to get the last message sent by a specific user for all chats he’s a part of. This is what I have so far (left join for debugging on null):

SELECT c.chatid,
       m.message
FROM   chat AS c
       INNER JOIN chat_user AS cu
               ON cu.chatid = c.chatid
       LEFT JOIN (SELECT *
                   FROM   chat_message
                   WHERE  chat_message.userid = 6
                   ORDER  BY created_at DESC
                   LIMIT  1) AS m
               ON m.chatid = c.chatid
WHERE  cu.userid = 6;

The problem with this query is that the LIMIT 1 makes chat_message results null beyond the first row of results.

Removing the limit returns the data but am getting all the messages vs just the latest one.

What am I missing here? I feel like the LIMIT might be in the wrong place. I also tried the following alternative with no success:

SELECT c.chatid,
       m.message,
       m.userid
FROM   chat AS c
       INNER JOIN chat_user AS cu
               ON cu.chatid = c.chatid
LEFT JOIN chat_message as m
       ON  m.chatid = c.chatid
       AND m.created_at = (SELECT MAX(created_at) from chat_message)
WHERE  cu.userid = 6; 

Expected:

chatid message userid
1   'Last message 1'    6
2   'Last message 2'    6

Actual:

chatid message userid
2   'Last message 2'    6
1   null null

Answer

Am trying to get the last message sent by a specific user for all chats he’s a part of.

SELECT cm.chatid, cm.message, cm.userid
FROM chat c JOIN
     chat_message cm
     ON m.chatid = cm.chatid
WHERE c.userid = 6 AND
      cm.created_at = (SELECT MAX(cm2.created_at)
                       FROM chat_message cm2
                       WHERE cm2.chatid = cm.chatid
                      );

To optimize performance, I would recommend indexes on:

• chat_message(chatid, created_at)
• chat(userid, chatid)