CODEWITHSUNDEEP
mysqlsql
Kaspar
Kaspar

Reputation: 80

Find conversation between one or more users

I have a problem with finding conversations between given user_ids.

SQL table:

+-------------------+
| conversation_user |
+-------------------+
| conversation_id   |
| user_id           |
+-------------------+

I've tried 

SELECT `conversation_id` FROM `conversation_user` WHERE `user_id` IN (X, Y) HAVING COUNT(*) = N

But it does not work correctly. Any idea how to select the correct conversation_id? A conversation can be between one or more users.

Edit:

+-----------------+---------+
| conversation_id | user_id |
+-----------------+---------+
|               1 |       1 | 
|               1 |       2 | 
+-----------------+---------+
|               2 |       1 | 
|               2 |       3 | 
+-----------------+---------+
|               3 |       1 | 
+-----------------+---------+
|               4 |       1 | 
|               4 |       2 | 
|               4 |       3 | 
+-----------------+---------+

Let's say I want to get the conversation between user 1 and 2. The result must be 1, not 1 and 4 or 4.

Upvotes: 1

Views: 159

Answers (3)

Kaspar
Kaspar

Reputation: 80

UPDATED! I resolved problem with this query.

SELECT cu.`conversation_id` 
FROM `conversation_user` cu 
INNER JOIN (
    SELECT `conversation_id`
    FROM `conversation_user` 
    WHERE `user_id` IN (X, Y) 
    GROUP BY `conversation_id` HAVING COUNT(*) = Z 
) cu2 ON cu.conversation_id=cu2.conversation_id
GROUP BY `conversation_id` 
HAVING COUNT(*) = Z;

Upvotes: 2

John Woo
John Woo

Reputation: 263733

I think you lack GROUP BY clause

SELECT `conversation_id` 
FROM `conversation_user` 
WHERE `user_id` IN (X, Y) 
GROUP BY conversation_id
HAVING COUNT(DISTINCT user_id) = N

or

SELECT `conversation_id` 
FROM `conversation_user` a
WHERE `user_id` IN (X, Y) 
GROUP BY conversation_id
HAVING COUNT(DISTINCT user_id) = 
    (
        SELECT COUNT(DISTINCT userid)
        FROM `conversation_user` b
        WHERE b.`conversation_id` = a.`conversation_id`
        GROUP BY b.`conversation_id`
    )

SQLFiddle Demo

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

    SELECT `conversation_id`,count(*) Number_of_conversations FROM `conversation_user` 
    WHERE `user_id` IN (X, Y) and 
   `user_id` not in(select `user_id` from `conversation_user` where `user_id` not in(X, Y))
    GROUP BY `conversation_id`
    HAVING COUNT(*) = 2

Upvotes: 0

Related Questions