SELECT WHERE ON an advanced way

Question

I am trying to check the database if a specific combination exists.

Table: conversations

+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
+----+

Table: conversations_users

+----+--------------+------+
| id | conversation | user |
+----+--------------+------+
| 1  | 1            | 1    |
| 2  | 1            | 2    |
| 3  | 2            | 1    |
| 4  | 2            | 2    |
| 5  | 2            | 3    |
| 6  | 2            | 4    |
| 7  | 3            | 2    |
| 8  | 3            | 3    |
| 9  | 4            | 2    |
| 10 | 4            | 4    |
+----+--------------+------+

Then I want to make a query to get the conversations where those users are in the same conversations:

Users: 1,2,3,4 (Only them, no else)

If there is a conversation where only those are in, I want to get the id of that conversation, and else the result should become 0

Anyone have any ideas how to do this trick?

Answer 1

The idea is to count the distinct users in a given conversation. If it matches the number of users you set in the IN clause, then you are sure that there is only the ones you search for:

SELECT id
FROM conversations_users
WHERE user in (1, 2, 3, 4)
GROUP BY id
HAVING COUNT(DISTINCT user) = 4

Note that this doesn't output conversations where only 3 of the 4 users appears. If you need these conversations too, then:

SELECT id
FROM conversations_users
WHERE user in (1, 2, 3, 4)
GROUP BY id
HAVING COUNT(DISTINCT user) <= 4

Answer 2

This is an example of a "set-within-sets" query. For these, I like to use group by with the having clause:

select conversation
from conversation_users cu
group by conversation
having SUM(user = 1) > 0 and
       sum(user = 2) > 0 and
       sum(user = 3) > 0 and
       sum(user = 4) > 0 and
       sum(user not in (1, 2, 3, 4)) = 0

Each condition of the having clause corresponds to one of the five conditions specified in the question:

• user 1 is in the conversation
• user 2 is in the conversation
• user 3 is in the conversation
• user 4 is in the conversation
• no other users are in the conversation

Answer 3

I think this is what you're looking for:

SELECT cu.conversation
FROM (select conversation, count(distinct user) usercnt 
      from conversations_users
     group by conversation) t
  JOIN conversations_users cu on t.conversation = cu.conversation
WHERE cu.user in (1, 2, 3, 4) AND t.usercnt = 4
GROUP BY cu.conversation
HAVING COUNT(DISTINCT cu.user) = 4

SQL Fiddle Demo

This uses a subquery to determine the overall number of users associated with each conversation. This is needed to ensure you don't have more users in the conversation than just 1,2,3 and 4.