Help me choose a dialogue on users. For example, there are values:

первый user_id = 1, второй user_id = 2.

db example

  • one
    ru.stackoverflow.com/q/518991/194569 - Mike
  • @Mike Not suitable, as there is no dialog_id value - QuodNephilim
  • one
    Not suitable You did not understand the response link. Perfectly fit ... the dialog_id is not an input, but a parameter returned by the query. - Akina
  • @Akina did not understand, help form a request - QuodNephilim
  • Select by user_id list, group by dialog_id, and leave for what COUNT (DISTINCT user_id) is equal to two. - Akina

1 answer 1

Try

 SELECT u.dialog_id FROM users u1 LEFT JOIN users u2 ON u2.dialog_id = u.dialog_id WHERE u.user_id = 1 AND u2.user_id = 2 AND u2.dialog_id IS NOT NULL
  • This query allows more than 2 users to be allowed as a result. - QuodNephilim
  • This condition was not in question. SELECT dialog_id FROM users WHERE 1 GROUP BY dialog_id HAVING COUNT (user_id) = 2 AND user_id IN (1,2) - Michael Mochulsky
  • This condition returns an error. - QuodNephilim 2:42
  • What kind of mistake? No errors. Tested on MySQL 5.7 - Michael Mochulsky
  • Unknown column 'user_id' in 'having clause' Query: SELECT dialog_id FROM dialog_member WHERE 1 GROUP BY dialog_id HAVING COUNT (user_id) = 2 AND user_id IN (1,2) - QuodNephilim