mysql - Generating the query -

i'm developing messages system.

basicly have 2 tables, conversations table keeps id , relations table keeps conversation id , user ids in 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 send new message i'm checking if there conversation between users. example, user (id 1) choose userid 2 send message have conversation , need add message conversation.

so, problem can not exist conversation id in 1 query.

i can conversation ids belongs users query;

select distinct conversationid relations conversationid in  (     select distinct conversationid relations userid in ( 1,2 ) )  result : 1,2,3,4 

and if run query;

select distinct conversationid relations conversationid in  (     select distinct conversationid relations userid in ( 1,2 ) ) , userid not in ( 1,2 )  result : 2,3,4 

but need "id : 1" conversation of these users.

how can id 1 query?

thanks

this problem called relational division

assuming userid unique every conversationid,

select  conversationid    conversationtable   userid in (1, 2) ,   -- <<== list of userid want find         exists         (             select  1                conversationtable b               a.conversationid = b.conversationid             group   conversationid             having  count(*) = 2 -- <<== number of userid on list         ) group   conversationid having  count(*) = 2 -- <<== number of userid on list 

• sqlfiddle demo
• sql of relational division