php - In MySQL, how do I select a result where the result contains every value I test for? -

check out sql fiddle simplified version of issue http://sqlfiddle.com/#!9/cf31d3/1

i have 2 tables - chat messages , chat recipients this:

sample chatmessages data:

sample chatrecipients data:

basically want query messages contain set of user ids - example, show messages exchanged between bob, susan, , chelsea. if pull new chat window user ids (1, 2, 3) best way messages involving 3 people?

here's simplified version of current query (which not produce correct result):

select   cm.message_id 'message_id',   cm.from_id    'from_id',   (select u.user_fname 'fname' users u u.user_id = cm.from_id) 'firstname',   (select u.user_lname 'lname' users u u.user_id = cm.from_id) 'lastname',   cm.chat_text  'chat_text'   chatmessages cm inner join   chatrecipients cr on   cm.message_id = cr.message_id inner join   users u on   cm.from_id = u.user_id   cm.from_id in ('1', '2', '3') ,   cr.user_id in ('1', '2', '3') 

i understand using 'in' operator not correct situation, i'm bit stuck. willing help!

edit:

my sample output returns every row of data of aforementioned user ids contained in , looks this:

my goal limit output messages every user id test associated message_id. example, if message_id 32 user_id 7 , user_id(s) 11 & 3, want retrieve record. conversely, if message_id 33 user_id 7 , user_id(s) 11 & 4 not want retrieve record.

the problem here message must either be:

• from user 1 , received 2, 3, ...n
• from user 2 , received 1, 3, ...n
• ...
• from user n , received 1, 2, ...n-1

and need query capable of scaling reasonably, i.e., no single join every recipient or things that.

let's start "from" part.

select m.* chatmessages m     from_id in ($users) 

now need know recipients these messages have.

select m.* chatmessages m     join chatrecipients r on (m.message_id = r.message_id)     from_id in ($users) 

recipients may or bad , i'm interested in how many are. so

select m.*,     count(*) total,     sum(if(user_id in ($users), 1, 0)) chatmessages m     join chatrecipients r on (m.message_id = r.message_id)     from_id in ($users) group m.message_id; 

finally

a message acceptable if it's between [1...n] users, means has n-1 recipients, n-1 of them good.

select m.*,     count(*) total,     sum(if(user_id in ({$users}), 1, 0) chatmessages m     join chatrecipients r on (m.message_id = r.message_id)     from_id in ({$users}) group m.message_id having total = , = {$n} 

test

in case 3 id's have $users = 1,2,3 , $n = 2

select m.*,     count(*) total,     sum(if(user_id in (1,2,3), 1, 0)) chatmessages m     join chatrecipients r on (m.message_id = r.message_id)     from_id in (1,2,3) group m.message_id having total = , = 2   message_id  from_id     chat_text 1           2           message susan bob , chelsea 2           3           message chelsea bob , susan 3           1           message bob chelsea , susan