GROUP BY and Select Max ID with Two Column Condition

Question

I'm trying to create chat bot, the logic is there are 2 user type, internal and external, and there are many user internal or external. When user external send a message, then all internal user can read and reply, so I create logic when a external user send a message, it will send to id 0 as mark.

TBL_USER
userId | name     | userType
----------------------------
10     | user10   | internal
11     | user11   | internal
2222   | user2222 | external
2223   | user2223 | external
2224   | user2224 | external

TBL_CHAT
chatId | chatFrom  | chatTo | chatValue
------------------------------------------------
1      | 2222      | 0      | this value chatId1
2      | 2222      | 0      | this value chatId2
3      | 2223      | 0      | this value chatId3
4      | 2223      | 0      | this value chatId4
5      | 2224      | 0      | this value chatId5
6      | 2224      | 0      | this value chatId6
7      | 10        | 2222   | this value chatId7
8      | 11        | 2223   | this value chatId8

I want select last chat summary for each external user, not sure the last chat from each user is outgoing message or ingoing message.

Have tried:

SELECT name, chatFrom, chatTo, chatId, chatValue, 
FROM TBL_CHAT INNER JOIN TBL_USER ON chatFrom=userId 
WHERE chatId IN (SELECT MAX(chatId) FROM TBL_CHAT 
WHERE chatTo=0 GROUP BY chatFrom) ORDER BY chatId DESC

Result (not like expected):

name     | chatFrom | chatTo | chatId | chatValue
----------------------------------------------------------
user2224 | 2224     | 0      | 6      | this value chatId6
user2223 | 2223     | 0      | 4      | this value chatId4
user2222 | 2222     | 0      | 2      | this value chatId2

Expected :

name     | chatFrom | chatTo | chatId | chatValue
----------------------------------------------------------
user2223 | 11       | 2223   | 8      | this value chatId8
user2222 | 10       | 2222   | 7      | this value chatId7
user2224 | 2224     | 0      | 6      | this value chatId6

How can I fix this?

Answer 1

SELECT name, chatFrom, chatTo, chatId, chatValue

FROM TBL_CHAT a

INNER JOIN TBL_USER b

ON a.chatFrom=b.userId

WHERE a.chatId = (SELECT MAX(chatId)

FROM TBL_CHAT c

WHERE c.chatFrom = b.userId)

Answer 2

You need for that to join the chat table twice

CREATE TABLE TBL_USER
    (`userId` int, `name` varchar(8), `userType` varchar(8))
;
    
INSERT INTO TBL_USER
    (`userId`, `name`, `userType`)
VALUES
    (10, 'user10', 'internal'),
    (11, 'user11', 'internal'),
    (2222, 'user2222', 'external'),
    (2223, 'user2223', 'external'),
    (2224, 'user2224', 'external')
;


CREATE TABLE TBL_CHAT
    (`chatId` int, `chatFrom` int, `chatTo` int, `chatValue` varchar(18))
;
    
INSERT INTO TBL_CHAT
    (`chatId`, `chatFrom`, `chatTo`, `chatValue`)
VALUES
    (1, 2222, 0, 'this value chatId1'),
    (2, 2222, 0, 'this value chatId2'),
    (3, 2223, 0, 'this value chatId3'),
    (4, 2223, 0, 'this value chatId4'),
    (5, 2224, 0, 'this value chatId5'),
    (6, 2224, 0, 'this value chatId6'),
    (7, 10, 2222, 'this value chatId7'),
    (8, 11, 2223, 'this value chatId8')
;

SELECT 
    name, tc.chatFrom, IFNULL(tc2.chatFrom,0), tc.chatId, COALESCE(tc2.chatValue,tc.chatValue) chatValue
FROM
    TBL_CHAT tc
        INNER JOIN
    TBL_USER ON tc.chatFrom = userId
            LEFT JOIN
    TBL_CHAT tc2 ON tc2.chatTo = tc.chatFrom 
WHERE
    tc.chatId IN (SELECT 
            MAX(chatId)
        FROM
            TBL_CHAT
        WHERE
            chatTo = 0
        GROUP BY chatFrom)
ORDER BY tc.chatId DESC

name     | chatFrom | IFNULL(tc2.chatFrom,0) | chatId | chatValue         
:------- | -------: | ---------------------: | -----: | :-----------------
user2224 |     2224 |                      0 |      6 | this value chatId6
user2223 |     2223 |                     11 |      4 | this value chatId8
user2222 |     2222 |                     10 |      2 | this value chatId7

db<>fiddle here