MySQL query with sort on two tables?

Question

MySQL

Hello! Please help me write a MySQL query correctly.
There are two tables.

users:
1. user_id
2. name
3. rating (int)
4. place (int)

subscribes (подписки на пользователей):
1. from_user_id (кто подписался)
2. to_user_id (на кого подписался)

You need to write a query that will sort users by three parameters:
By rating (rating), the higher the rating, the higher the user
By the number of subscribers, the more subscribers, the higher the user
By place (place), the higher the place (i.e. the lower the number), the higher the user
I wrote the following variant:

SELECT subscribes.to_user_id, count(subscribes.to_user_id) as cnt, users.* 
FROM subscribes, users
WHERE users.user_id = subscribes.to_user_id
GROUP BY subscribes.to_user_id
ORDER BY users.rating DESC, cnt DESC, users.place ASC

And here everything is fine, sorting works, but there is one catch. Only users who have at least one subscriber are taken into account, the rest are not shown at all. I understand what this is connected with - "WHERE users.user_id = subscribes.to_user_id", but there are no thoughts how to solve it.
Please tell me how you can change the request so that users without subscribers are also taken into account. Thank you.

Answer 1

SELECT u.*, COUNT(s.*) cnt
FROM users u
LEFT JOIN subscribes s ON u.user_id = s.to_user_id
GROUP BY u.user_id
ORDER BY u.rating DESC, cnt DESC, u.place ASC

try like this