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

Question

Check out this SQL Fiddle for a simplified version of my issue http://sqlfiddle.com/#!9/cf31d3/1

I have 2 tables - chat messages and chat recipients that look like this:

Sample ChatMessages data:

Sample ChatRecipients data:

Basically I want to query only messages that contain a set of user IDs - for example, show only messages exchanged between Bob, Susan, and Chelsea. If I pull up a new chat window with user IDs (1, 2, 3) what is the best way to get messages ONLY involving those 3 people?

Here's a simplified version of my current query (which does not produce the correct result):

SELECT
  cm.message_id as 'message_id',
  cm.from_id    as 'from_id',
  (SELECT u.user_fname as 'fname' from Users u where u.user_id = cm.from_id) as 'firstName',
  (SELECT u.user_lname as 'lname' from Users u where u.user_id = cm.from_id) as 'lastName',
  cm.chat_text  as 'chat_text'
FROM
  ChatMessages cm
INNER JOIN
  ChatRecipients cr
ON
  cm.message_id = cr.message_id
INNER JOIN
  Users u
ON
  cm.from_id = u.user_id
WHERE
  cm.from_id in ('1', '2', '3')
AND
  cr.user_id in ('1', '2', '3')

I understand that using the 'IN' operator is not correct for this situation, but I'm a bit stuck. Thanks to anyone willing to help!

EDIT:

My sample output returns every row of data that any of the aforementioned user IDs are contained in and looks like this:

My goal is to limit the output to only messages where EVERY user ID I test for is associated with a message_id. For example, if message_id 32 is FROM user_id 7 and TO user_id(s) 11 & 3, I want to retrieve that record. Conversely, if message_id 33 is FROM user_id 7 and to user_id(s) 11 & 4 I do not want to retrieve that record.

Answer 1

The problem here is that your message must either be:

• from user 1 and received by 2, 3, ...N
• from user 2 and received by 1, 3, ...N
• ...
• from user N and received by 1, 2, ...N-1

and you need a query capable of scaling reasonably, i.e., no single JOIN for every recipient or things like that.

Let's start with the "from" part.

SELECT m.* FROM ChatMessages AS m
    WHERE from_id IN ($users)

Now I need to know what recipients these messages have.

SELECT m.* FROM ChatMessages AS m
    JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
    WHERE from_id IN ($users)

Recipients may be good or bad and I'm interested in how many they are. So

SELECT m.*,
    COUNT(*) AS total,
    SUM(IF(user_id IN ($users), 1, 0)) AS good
FROM ChatMessages AS m
    JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
    WHERE from_id IN ($users)
GROUP BY m.message_id;

Finally

A message is acceptable if it's between my [1...N] users, which means that it has exactly N-1 recipients, N-1 of them good.

SELECT m.*,
    COUNT(*) AS total,
    SUM(IF(user_id IN ({$users}), 1, 0) AS good
FROM ChatMessages AS m
    JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
    WHERE from_id IN ({$users})
GROUP BY m.message_id
HAVING total = good AND good = {$n}

Test

In this case with three id's we have $users = 1,2,3 and $n = 2

SELECT m.*,
    COUNT(*) AS total,
    SUM(IF(user_id IN (1,2,3), 1, 0)) AS good
FROM ChatMessages AS m
    JOIN ChatRecipients AS r ON (m.message_id = r.message_id)
    WHERE from_id IN (1,2,3)
GROUP BY m.message_id
HAVING total = good AND good = 2


message_id  from_id     chat_text
1           2           Message from Susan to Bob and Chelsea
2           3           Message from Chelsea to Bob and Susan
3           1           Message from Bob to Chelsea and Susan

Answer 2

add:

'GROUP BY message_id HAVING COUNT(DISTINCT cr.user_id)=2'

The general case in php instead of 2: count($otherUserIds)

See it in action: http://sqlfiddle.com/#!9/bcf1b/13 See also some explanation: Matching all values in IN clause