MYSQL SELECT WITH LEFT JOIN LIMIT 1

Question

I have a problem: I want to select all babble-rooms where user1 or user2 are a specific user id. (SELECT * FROM babble WHERE user1 = '$user' OR user2 = '$user')

But I also want to get the last entry of babble_messages where the babble ID is equal to babble_id.

My database structure:

Table babble:
ID | user1 | user2 | created

Table babble_messages:
ID | sender | babble_id | created | seen | message

My attempt is:

SELECT 
    * 
FROM 
    babble
LEFT JOIN (
    SELECT
        babble_messages.ID AS last_id, 
        babble_messages.sender AS last_sender, 
        babble_messages.babble_id, 
        babble_messages.created AS last_created, 
        babble_messages.seen AS last_seen, 
        babble_messages.message AS last_message
    FROM 
        babble_messages 
    ORDER BY 
        created DESC LIMIT 1
    ) babble_messages ON 
    babble.ID = babble_messages.babble_id 
WHERE 
    user1 = '$user' OR user2 = '$user' 
ORDER BY 
    last_created DESC

This outputs empty entries for the LEFT JOIN table. I think this happens because I select in the subquery just the last entry of babble_messages... I tried to add a WHERE tag into the subquery but that throws an error:

SELECT 
    * 
FROM 
    babble
LEFT JOIN (
    SELECT
        babble_messages.ID AS last_id, 
        babble_messages.sender AS last_sender, 
        babble_messages.babble_id, 
        babble_messages.created AS last_created, 
        babble_messages.seen AS last_seen, 
        babble_messages.message AS last_message
    FROM 
        babble_messages 
    WHERE 
        babble.ID = babble_messages.babble_id* 
    ORDER BY 
        created DESC 
    LIMIT 
        1
    ) babble_messages ON 
    babble.ID = babble_messages.babble_id 
WHERE 
    user1 = '$user' OR user2 = '$user' 
ORDER BY 
    last_created DESC

In the subquery mysql does not know the "babble.ID". How can I select just the last entry of babble_messages?

Of course I can use multiple request but I want to make just one request, because it is faster.

I hope this is enough information. :D Thanks for reading and hopefully answering :)

Have a nice day hadome

Answer 1

This may be somewhat slow if the number of users is massive, especially with the OR in there.

SELECT
    ID
    ,user1
    ,user2
    ,created
    ,last_id
    ,last_sender
    ,babble_id
    ,last_created
    ,last_seen
    ,last_message
FROM
(
    SELECT
        ID
        ,user1
        ,user2
        ,created
        ,(SELECT ID FROM babble_messages WHERE babble_ID = user1 ORDER BY created DESC LIMIT 1) AS babble_message_id
    FROM babble
    WHERE user1 = 1
) a
LEFT JOIN (
    SELECT 
        ID last_id
        ,sender last_sender
        ,babble_id
        ,created last_created
        ,seen last_seen
        ,message last_message
    FROM babble_messages
) b ON a.babble_message_id = b.last_id
ORDER BY last_created DESC