MySQL count results from JOIN with where clause

I’ve got two tables.

chats:
id, name, type, deleted_at

user_chat:
id, user_id, chat_id, deleted_at

What I’m trying to do is to select a private chat with a user ID 1, where it’s the only participant in the chat, avoiding sub-queries.

What I tried:

SELECT count(user_chat.id) as count_users, chats.*
FROM chats
    LEFT OUTER JOIN user_chat on user_chat.chat_id = chats.id
WHERE chats.type = 'private'
    AND user_chat.user_id = 1
    AND user_chat.deleted_at IS NULL
    AND chats.deleted_at IS NULL
GROUP BY chats.id
HAVING count_users = 1;

With all variants of group, having, etc. with no luck. Please, advice.

Go to Source
Author: Tim Mishutin