By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE message_members
(`relation_id` int, `user_id` int, `date` datetime, `seen` int)
;
INSERT INTO message_members
(`relation_id`, `user_id`, `date`, `seen`)
VALUES
(2, 1, '2020-11-18 19:16:54', 0),
(5, 4, '2020-11-18 19:53:34', 0),
(5, 6, '2020-11-18 19:53:34', 0),
(14, 1, '2020-11-19 00:02:44', 0),
(14, 3, '2020-11-19 00:02:44', 0),
(19, 1, '2020-11-19 00:16:32', 0),
(19, 3, '2020-11-19 00:16:32', 0),
(20, 3, '2020-11-19 00:17:37', 0),
(20, 4, '2020-11-19 00:17:37', 0),
(21, 1, '2020-11-19 00:18:09', 0),
(21, 3, '2020-11-19 00:18:09', 0),
(21, 6, '2020-11-19 00:18:09', 0),
(22, 1, '2020-11-19 00:18:45', 0),
(22, 4, '2020-11-19 00:18:45', 0),
(22, 6, '2020-11-19 00:18:45', 0),
(23, 1, '2020-11-19 00:19:06', 0),
(23, 3, '2020-11-19 00:19:06', 0),
(23, 4, '2020-11-19 00:19:06', 0),
(24, 3, '2020-11-19 00:19:42', 0),
(24, 4, '2020-11-19 00:19:42', 0),
(24, 6, '2020-11-19 00:19:42', 0),
(25, 3, '2020-11-19 01:41:44', 0),
(25, 5, '2020-11-19 01:41:44', 0),
(43, 1, '2022-02-28 17:38:34', 0),
(43, 54, '2022-02-28 17:38:35', 0),
(46, 1, '2022-03-16 23:24:43', 0),
(46, 5, '2022-03-16 23:24:43', 0),
(47, 1, '2022-03-16 23:25:51', 0),
SELECT mm.relation_id
, GROUP_CONCAT(mm.user_id) AS User_List
FROM message_members mm
WHERE mm.user_id IN (1,3)
AND NOT EXISTS (
SELECT NULL
FROM message_members ex
WHERE ex.relation_id = mm.relation_id
AND ex.user_id NOT IN (1,3)
)
GROUP BY mm.relation_id
HAVING COUNT(DISTINCT mm.user_id) = 2
;
| relation_id | User_List |
|---|---|
| 14 | 1,3 |
| 19 | 1,3 |