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