MySQL query to get data from multiple rows

Question

I need to select a conversation_id from a MySQL table where the users in a conversation is the exact match of users provided in an array such as (2, 5) - which should return 1 as the conversation id.

Extract of the db table as follows:

user_ids (2, 5)

conversation_users:

conversation_id      user_id
1                    2
1                    5
2                    2
2                    6

Can this be done in a single query?

EDIT - 26.06.2014

Actually, after doing some testing this does not always work correctly. If there are more users that belong to a conversation than there are in the array, it will still return all the conversations those users belong to.

I would like to get an exact match of users in the array to users in the conversation.

fiddle

In the example below, conversations 6 and 7 are both returned but it should not return any rows:

SELECT `conversation_id` FROM `conversation_user` WHERE `user_id` IN (70, 426)
GROUP BY `conversation_id` HAVING COUNT(DISTINCT `user_id`) = 2;


conversation_user_id  conversation_id user_id
14                    6               70
15                    6               29
16                    6               442
17                    6               425
18                    6               426
19                    7               70
20                    7               442
21                    7               426
22                    7               499
23                    7               425
24                    7               29

Similar answer from today sqlfiddle.com/#!2/d856b/3

Abhik Chakraborty
– Abhik Chakraborty

2014-06-24 13:53:01 +00:00
Commented Jun 24, 2014 at 13:53 — Abhik Chakraborty
– Abhik Chakraborty, Commented Jun 24, 2014 at 13:53

Aziz Shaikh · Accepted Answer · 2014-06-26 14:06:33Z

2

Yes this is possible by using a combination of WHERE, GROUP BY, and HAVING clauses, like this:

SELECT conversation_id 
FROM conversation_users
WHERE user_id IN (2,5)
GROUP BY conversation_id
HAVING COUNT(DISTINCT user_id) = 2

2nd Option:

In case, there are more than two records (users) in one conversation then you have couple of alternatives. One option is given below, in which WHERE clause is removed and GROUP_CONCAT is used, like this:

SELECT `conversation_id` FROM `conversation_user` 
GROUP BY `conversation_id` 
HAVING COUNT(DISTINCT `user_id`) = 2
AND GROUP_CONCAT(DISTINCT `user_id` ORDER BY `user_id` ASC SEPARATOR ',') = '70,426'

However, this may not be a very flexible approach because it expects the user list to be a sorted comma-separated string.

Working Fiddle: http://sqlfiddle.com/#!2/4c82d/13

3rd Option:

Instead of GROUP_CONCAT (as in the 2nd option above), use a sub-query to get only those conversations which have only 2 users. Then join this sub-query with the main table and apply the same filter (WHERE and GROUP BY) as used in the 1st option given above, like this:

SELECT `conversation_id` 
FROM `conversation_user` 
JOIN (
  SELECT `conversation_id` t_cid FROM `conversation_user` 
  GROUP BY `conversation_id` 
  HAVING COUNT(DISTINCT `user_id`) = 2
) t
ON t.t_cid = `conversation_user`.`conversation_id`
WHERE `user_id` IN (70, 426)
GROUP BY `conversation_id` 
HAVING COUNT(DISTINCT `user_id`) = 2

Should be a better approach because it does not rely on the sorted comma-separated list required in the 2nd option.

Working Fiddle: http://sqlfiddle.com/#!2/4c82d/21

edited Jun 26, 2014 at 14:06

answered Jun 24, 2014 at 14:00

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Asa Carter Over a year ago

Is the value of "2" in HAVING COUNT(DISTINCT user_id) = 2 the count of number of users in the array?

Aziz Shaikh Over a year ago

@AsaCarter yes, you are correct. The value "2" is the number of users in the array.

Asa Carter Over a year ago

Edited question above. The answer is not working correctly when there a more users in the conversation.

Aziz Shaikh Over a year ago

@AsaCarter Based on your edits, I have updated my answer above.

Collectives™ on Stack Overflow

MySQL query to get data from multiple rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related