I’ve got two tables.
id, name, type, deleted_at
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