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?

It would be easier for us to help you if you set up a [SQL Fiddle](http://sqlfiddle.com/) — John Conde, Apr 30 '13 at 13:22
Because of the existence of user 5, I have assumed that you do not require ALL four users (1,2,3,4) to be in the conversations you are looking for, but instead, you are looking for any conversation that includes any combination of the ( 1,2,3,4) users. Can you clarify this? — gillyspy, Apr 30 '13 at 14:27

score 3 · Answer 1 · answered Apr 30 '13 at 13:22

3

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

answered Apr 30 '13 at 13:22

Cyril Gandon

16,830
14
78
122

Sorry for the late reply. I have been sick, so wasn't at work. Well, this works very good, but the only problem is that if I have the following: http://sqlfiddle.com/#!2/859360/1 problem is that it also select conversations where there are more users in, so not only the selected. If you understand. – Mark Topper May 08 '13 at 08:13

Muhammad Raheel · Answer 2 · 2013-04-30T13:46:24.310

SELECT
  cs.conversation,
  IF(csl.total = 4,'yes','no') AS AllIn
FROM conversations_users AS cs
  LEFT JOIN (
                SELECT 
                    conversation , 
                    COUNT(DISTINCT user) AS total 
                FROM conversations_users 
                WHERE user IN (1,2,3,4) 
                GROUP BY conversation
            ) AS csl
    ON csl.conversation = cs.conversation
GROUP BY cs.conversation

SQL Fiddle Demo

Output

| CONVERSATION | ALLIN |
------------------------
|            1 |    no |
|            2 |   yes |
|            3 |    no |
|            4 |    no |

This will give you all conversation ids and their status

Modified

| CONVERSATION | ALLIN |
------------------------
|            1 |     0 |
|            2 |     2 |
|            3 |     0 |
|            4 |     0 |

While a fine solution, my understanding was you wanted to exclude conversations which had people in other than your specified 4? Or did I read your original question incorrectly? — Kickstart, May 08 '13 at 11:55

score 2 · Answer 3 · answered Apr 30 '13 at 13:33

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.

fthiella · Answer 4 · 2013-04-30T18:45:14.393

2

If I understand your question correctly, you could use this:

SELECT
  conversation
FROM
  conversations_users
GROUP BY
  conversation
HAVING
  COUNT(
    DISTINCT CASE WHEN user IN (1,2,3,4) THEN user END
  )=4 AND
  COUNT(DISTINCT user)=4

edited Apr 30 '13 at 18:45

answered Apr 30 '13 at 13:43

fthiella

48,073
15
90
106

score 2 · Answer 5 · edited May 23 '17 at 12:18

Assuming you have a users table as well:

SELECT id
FROM conversations AS c
WHERE NOT EXISTS
      ( SELECT *
        FROM users AS u
        WHERE u.id IN (1, 2, 3, 4)
          AND NOT EXISTS
              ( SELECT *
                FROM conversations_users AS cu 
                WHERE cu.user = u.id
                  AND cu.conversation = c.id
              )
      ) 
  AND NOT EXISTS 
      ( SELECT *
        FROM conversations_users AS co        -- and only them
        WHERE co.conversation = c.id 
          AND co.user NOT IN (1, 2, 3, 4)
      ) ;

If you don't have a users table or you don't like to use it (can'see why but anyway), you can replace this part:

WHERE NOT EXISTS
      ( SELECT *
        FROM users AS u
        WHERE u.id IN (1, 2, 3, 4)
          AND NOT EXISTS

with:

WHERE NOT EXISTS
      ( SELECT *
        FROM (SELECT 1 AS id UNION SELECT 2 UNION
              SELECT 3       UNION SELECT 4) AS u
        WHERE NOT EXISTS

The above query, while being general will not be very efficient in MySQL (blame the double nesting and the naive optimizer). The GROUP BY / COUNT way is probably a level more efficient - but please test with your data. You can also find many more ways (more than 10) to answer this kind of question, in this answer: How to filter SQL results in a has-many-through relation Some of them do not work in MySQL but many do. I'd expect queries 5 and 6 there to be quite efficient in MySQL (a level more efficient than the group by queries).

Your case has a difference, you want exact relational division while that question/answers are about (simple) relational division, so you could write 5 like this:

SELECT id
FROM conversations AS c
WHERE  EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 1)
AND    EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 2)
AND    EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 3)
AND    EXISTS (SELECT * FROM conversations_users AS cu
               WHERE  cu.conversation = c.id AND cu.user = 4)
AND    NOT EXISTS (SELECT * FROM conversations_users AS cu
                   WHERE  cu.conversation = c.id AND cu.user NOT IN (1,2,3,4))

+1 relational division 1.0. Next step: find (and enumerate) equivalence groups of users that share the same set of conversations ;-) — wildplasser, Apr 30 '13 at 13:52
Thanks a lot, but the `users` table is not needed for this. As the users ID's are in the `converstations_users` table. — Mark Topper, May 08 '13 at 08:15
I didn't say it was needed. But if you do have a users table, the query is easier to write. — ypercubeᵀᴹ, May 08 '13 at 08:22

gillyspy · Answer 6 · 2013-04-30T14:26:27.763

Keep the query and joins simple and easy to read.

Because of the existence of user 5, I have assumed that you do not require ALL 4 users (1,2,3,4) to be in the conversations you are looking for, but instead, any conversation that only includes any combination of those 4 users.

DEMO

select distinct
    cu.conversation
from
    conversations_users cu
        left join
    conversations_users cu2 ON cu.conversation = cu2.conversation
where
    cu.user in (1 , 2, 3, 4)
        and cu2.user in (1 , 2, 3, 4)
        and cu.user != cu2.user /* include this clause if you need to exclude conversations of a user with themselves */

Let me know if you instead want the conversations where only all 4 users are involved.

Are you wanting to delete the other conversations? When you say "result should become 0" are you referring to the number of rows or value of the conversation id? If it's the latter then use:

select distinct
    case
        when
            cu.user in (1 , 2, 3, 4)
                and cu2.user in (1 , 2, 3, 4)
        then
            cu.conversation
        else 0
    end conversation
from
    conversations_users cu
        left join
    conversations_users cu2 ON cu.conversation = cu2.conversation
where
    1 = 1 
            and cu.user != cu2.user /* include this clause if you need to exclude conversations of a user with themselves */

score 2 · Answer 7 · answered Apr 30 '13 at 14:18

2

SELECT ID FROM CONVERSATIONS WHERE ID IN 
(SELECT CONVERSATIONS FROM CONVERSATION_USERS 
GROUP BY CONVERSATIONS HAVING COUNT(DISTINCT USER) >= 2)

answered Apr 30 '13 at 14:18

Androidz

413
1
6
19

Thanks. But this does not include the ID's of the users anywhere. So it might just select all conversations where there are 2 users in? – Mark Topper May 08 '13 at 08:14

score 2 · Accepted Answer · answered Apr 30 '13 at 14:30

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

Kickstart · Answer 9 · 2013-05-08T15:02:39.383

If I read your requirements correctly you want a the id of any conversation where the only people in it are (for example) 1,2,3 and 4, and all of those people are in it. If not you want 0 returning for that conversation.

If so then something like this

SELECT CASE WHEN MatchCount = 4 AND UnMatchCount IS NULL THEN conversations.id ELSE 0 END
FROM conversations
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS MatchCount FROM conversations_users WHERE user IN (1,2,3,4) GROUP BY conversation) Sub1 ON conversations.id = Sub1.conversation
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS UnMatchCount FROM conversations_users WHERE user NOT IN (1,2,3,4) GROUP BY conversation) Sub2 ON conversations.id = Sub2.conversation

EDIT - Modified version of the above query to only bring back the ids of conversations where there are only those 4 users involved. Playing around this seems to be a pretty efficient way of doing it.

SELECT conversations.id 
FROM conversations
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS MatchCount FROM conversations_users WHERE user IN (1,2,3,4) GROUP BY conversation) Sub1 ON conversations.id = Sub1.conversation
LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS UnMatchCount FROM conversations_users WHERE user NOT IN (1,2,3,4) GROUP BY conversation) Sub2 ON conversations.id = Sub2.conversation
WHERE MatchCount = 4 
AND UnMatchCount IS NULL

Maybe i did something wrong, but when I use this and print out the output, I just get the following: `[CASE WHEN MatchCount = 4 AND UnMatchCount IS NULL THEN conversations.id ELSE 0 END] => 0` — Mark Topper, May 08 '13 at 08:17
Just double checked it and it does work. It puts out the id if there are just those 4 people in the conversation, otherwise zero. You can easily give an alias name to the returned column if you want. — Kickstart, May 08 '13 at 09:38

SELECT WHERE ON an advanced way

9 Answers9