I have two tables:

CREATE TABLE `tbl_chat_messages` (
  `cmid` int(11) NOT NULL,
  `from_user_id` int(11) NOT NULL,
  `to_user_id` int(11) NOT NULL,
  `chat_message` text NOT NULL,
  `offer_mesage` text NOT NULL,
  `message_type` int(11) NOT NULL DEFAULT 0 COMMENT '0 = text 1 = image 2= offer',
  `timestamp` datetime NOT NULL DEFAULT current_timestamp(),
  `status` int(11) NOT NULL DEFAULT 0 COMMENT '0 = not read 1 = read'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tbl_chat_messages` (`cmid`, `from_user_id`, `to_user_id`, `chat_message`, `offer_mesage`, `message_type`, `timestamp`, `status`) VALUES
(1, 5, 1, 'Hello Rajubhai', '', 0, '2022-03-21 22:46:29', 0);

CREATE TABLE `tbl_users` (
  `id` int(11) UNSIGNED NOT NULL,
  `username` varchar(255) NOT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `profile_image` varchar(255) NOT NULL DEFAULT 'default.png',
  `online` int(10) NOT NULL DEFAULT 0,
  `current_session` int(11) NOT NULL DEFAULT 0,
  `connection_id` varchar(50) NOT NULL,
  `connected_with` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tbl_users` (`id`, `username`, `full_name`, `profile_image`, `online`, `current_session`, `connection_id`, `connected_with`) VALUES
(1, 'user1', 'USER 1', 'default.png', 0, 0, '', 0),
(2, 'user2', 'USER 2', 'default.png', 0, 0, '', 0),
(3, 'user3', 'USER 3', 'default.png', 0, 0, '', 0),
(4, 'user4', 'USER 4', 'default.png', 0, 0, '', 0),
(5, 'user5', 'USER 5', 'default.png', 0, 0, '', 0);

I am looking to get a list of all users who have talked before to each other or have sent message to each other.

For now I have 1 record in tbl_chat_messages sent from_user_id = 5 and to_user_id = 1.

So I need to get two users called user 1 and user 5 with my query but its giving me only one user called 5. My query is like below

SELECT u.id,
       u.username,
       u.profile_image,
       u.connected_with,
       u.online,
      (    SELECT COUNT(*)
           FROM tbl_chat_messages
           WHERE to_user_id = 5
           AND from_user_id = u.id
           AND status = 5
      ) AS count_status
FROM `tbl_chat_messages` AS m
         LEFT JOIN tbl_users AS u ON m.from_user_id = u.id
WHERE m.from_user_id = 5
  OR  m.to_user_id = 5
GROUP BY u.id

Actual Result:

Expected Result

Let me know if anyone can help me for correct my query.

GOAL : If I am user1, When My Chat Page Load, I want get user list who have sent me message before or I have sent message before instead List of all users.

Thanks a lot!

CodePudding user response：

The JOIN needs to include both the from_user_id and to_user_id columns. Also, try a conditional SUM() instead of a subquery. For example, if 5 is the current user id:

SELECT u.id
      , u.username
      , u.profile_image
      , u.connected_with
      , u.online
      , SUM( IF(m.status = 0, 1, 0) ) AS count_unread
FROM tbl_chat_messages AS m 
         LEFT JOIN tbl_users AS u 
         ON m.from_user_id = u.id
         OR m.to_user_id = u.id
WHERE m.from_user_id = 5
OR    m.to_user_id = 5
GROUP BY u.id

Results: