CODEWITHSUNDEEP
mysqlsql
rcpayan
rcpayan

Reputation: 535

Generating the query

I'm developing the messages system.

Basicly I have 2 tables, conversations table keeps only id and relations table keeps conversation id and user ids who is in the conversation.

My relations table;

|----------------|--------|
| CONVERSATIONID | USERID |
|----------------|--------|
|       1        |   1    |
|       1        |   2    |
|       2        |   2    |
|       2        |   3    |
|       3        |   1    |
|       3        |   2    |
|       3        |   3    |
|       3        |   4    |
|       4        |   3    |
|       4        |   1    |
|       4        |   2    |
|----------------|--------|

When user want to send new message I'm checking if there is conversation between users. For example, user (id 1) choose userid 2 to send a message but they already have the conversation and need to add message to this conversation.

So, my problem is I can not get the exist conversation id in one query.

I can get conversation ids that belongs users with this query;

SELECT DISTINCT CONVERSATIONID FROM RELATIONS
WHERE CONVERSATIONID IN 
(
    SELECT DISTINCT CONVERSATIONID FROM RELATIONS WHERE USERID IN ( 1,2 )
)

result : 1,2,3,4

And if I run this query;

SELECT DISTINCT CONVERSATIONID FROM RELATIONS
WHERE CONVERSATIONID IN 
(
    SELECT DISTINCT CONVERSATIONID FROM RELATIONS WHERE USERID IN ( 1,2 )
)
AND USERID NOT IN ( 1,2 )

result : 2,3,4

But I need "id : 1" that is the conversation of these users.

How can I get this id with one query?

Thanks

Upvotes: 1

Views: 61

Answers (1)

John Woo
John Woo

Reputation: 263723

This problem is called Relational Division

Assuming UserID is unique for every CONVERSATIONID,

SELECT  CONVERSATIONID
FROM    conversationTable a
WHERE   UserID IN (1, 2) AND   -- <<== list of UserID you want to find
        EXISTS
        (
            SELECT  1
            FROM    conversationTable b
            WHERE   a.CONVERSATIONID = b.CONVERSATIONID
            GROUP   BY CONVERSATIONID
            HAVING  COUNT(*) = 2 -- <<== number of userID on the list
        )
GROUP   BY CONVERSATIONID
HAVING  COUNT(*) = 2 -- <<== number of userID on the list

Upvotes: 3

Related Questions