Как сформировать SQL запрос для поиска в двух таблицах с условиями?

Question

[Alex47517]

Имеется 2 таблицы:
conversations: id, name, last_update
conversationmembers: id, chat_id (ссылка на conversations.id), user (id пользователя), rank, notifications
===
Мне необходимо реализовать проверку на существование приватного чата.
Чаты лс создаются с conversations.name = 'pm'
При создании нового чата происходит следущее:
- создается строка в таблице conversations
- создаются 2 строки собеседников в conversationmembers, chat_id которых ссылается на только что созданный conversations.id
В наличии есть данные только id профилей собеседников, которых необходимо проверить.

Пробовал такой запрос:
SELECT `id` FROM conversations, conversationmembers WHERE conversations.name = 'pm' AND conversations.id = conversationmembers.chat_id AND conversationmembers.user = $user['id']

Но он возвращает результат если существует хоть один чат у $user['id']. Возможно нужно просто добавить еще проверку $user1['id']...

Answer 1

[Дмитрий]

Выведет всех пользователей участвующих в приватных разговорах.

select * from conversationmembers where exists(select 1 from conversations where name = 'pm' and conversationmembers.chat_id = conversations.id);

Comments

[Alex47517]

SELECT * FROM conversationmembers WHERE EXISTS(SELECT 1 FROM conversations WHERE name = 'pm' AND conversationmembers.chat_id = conversations.id) AND conversationmembers.user IN ([ид_юзера1], [ид_юзера2])

Осталось только проверить есть ли в выборке 2 строки с одинаковым chat_id, реально это в sql запросе сделать?

[Дмитрий]

Alex47517,

SELECT chat_id FROM conversationmembers WHERE EXISTS(SELECT 1 FROM conversations WHERE name = 'pm' AND conversationmembers.chat_id = conversations.id) AND conversationmembers.user IN ([ид_юзера1], [ид_юзера2]) group by chat_id having count(*) = 2;

[Alex47517]

Дмитрий, Уже сам пришел к подобному варианту :)

SELECT COUNT(conversationmembers.chat_id) FROM conversationmembers WHERE EXISTS(SELECT 1 FROM conversations WHERE name = 'pm' AND conversationmembers.chat_id = conversations.id) AND conversationmembers.user IN (1, 17) GROUP BY conversationmembers.chat_id HAVING COUNT(conversationmembers.chat_id) > 1