How to select only those users with whom there are messages?

Question

MySQL

Once again I ask for help! I am implementing a message system on the site and everything would be fine - but for some reason the developer decided to show all users in the list of dialogs at once - even those to whom neither we wrote nor they wrote to us. I would really like to fix this (the issue was created there a long time ago - but the author disappeared ().

message table - actually only two fields are of interest - from whom and to whom

`messages` (
  `id` int(11) NOT NULL,
  `from_id` int(11) DEFAULT NULL,
  `whom_id` int(11) NOT NULL,
  `message` varchar(750) NOT NULL,
  `status` int(11) DEFAULT '0',
  `is_delete_from` int(11) DEFAULT '0',
  `is_delete_whom` int(11) DEFAULT '0',
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL
)

and now the request itself is quite large

SELECT
   `usr`.`id`,
   `usr`.`username` AS `username`,
   `msg`.`cnt` AS `cnt_mess`,
   `msg_`.`cnt_` AS `cnt_mess_all` 
FROM
   `user` `usr` 
   LEFT JOIN
      (
         SELECT
            `from_id`,
            count(id) AS `cnt` 
         FROM
            `messages` 
         WHERE
            (
               `status` = 1
            )
            AND 
            (
               `whom_id` = 9
            )
         GROUP BY
            `from_id`
      )
      `msg` 
      ON usr.id = msg.from_id 
   LEFT JOIN
      (
         SELECT
            `from_id`,
            `whom_id`,
            count(id) AS `cnt_` 
         FROM
            `messages` 
         WHERE
            (
               `from_id` = 9
            )
            OR 
            (
               `whom_id` = 9
            )
         GROUP BY
            `from_id`
      )
      `msg_` 
      ON usr.id = msg_.from_id 
      or usr.id = msg_.whom_id 
WHERE
   `usr`.`id` != 9 
ORDER BY
   `msg`.`cnt` DESC,
   `usr`.`username` DESC

the first join takes all unread messages `usr`.`id` = 9 and works correctly, but I already tried the second one

and the result is not clear how it turned out 3 in the cnt_mess_all field, because according to all rows from the table

there should be 1

, ideally in cnt_mess_all should be the total number of lines of correspondence between users i.e.

user9
- user3 = 2
user9 - user6 = 2
user9 - user7 = 1

Thanks in advance for your help. I already googled honestly ((

Answer 1

GROUP BY `from_id`

You are taking two columns and grouping only one of them. This is where the result comes from.
GROUP BY `from_id`, `whom_id`
I'm not sure, but it seems like it should help.