sql - MySQL query to get data from multiple rows -

sql - MySQL query to get data from multiple rows -

i need select conversation_id mysql table users in conversation exact match of users provided in array such (2, 5) - should homecoming 1 conversation id.

extract of db table follows:

user_ids (2, 5) conversation_users: conversation_id user_id 1 2 1 5 2 2 2 6

can done in single query?

edit - 26.06.2014

actually, after doing testing not work correctly. if there more users belong conversation there in array, still homecoming conversations users belong to.

i exact match of users in array users in conversation.

fiddle

in illustration below, conversations 6 , 7 both returned should not homecoming rows:

select `conversation_id` `conversation_user` `user_id` in (70, 426) grouping `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

yes possible using combination of where, group by, , having clauses, this:

select conversation_id conversation_users user_id in (2,5) grouping conversation_id having count(distinct user_id) = 2

2nd option:

in case, there more 2 records (users) in 1 conversation have couple of alternatives. 1 alternative given below, in where clause removed , group_concat used, this:

select `conversation_id` `conversation_user` grouping `conversation_id` having count(distinct `user_id`) = 2 , group_concat(distinct `user_id` order `user_id` asc separator ',') = '70,426'

however, may not flexible approach because expects user list sorted comma-separated string.

working fiddle: http://sqlfiddle.com/#!2/4c82d/13

3rd option:

instead of group_concat (as in 2nd alternative above), utilize sub-query conversations have 2 users. bring together sub-query main table , apply same filter (where , group by) used in 1st alternative given above, this:

select `conversation_id` `conversation_user` bring together ( select `conversation_id` t_cid `conversation_user` grouping `conversation_id` having count(distinct `user_id`) = 2 ) t on t.t_cid = `conversation_user`.`conversation_id` `user_id` in (70, 426) grouping `conversation_id` having count(distinct `user_id`) = 2

should improve approach because not rely on sorted comma-separated list required in 2nd option.

working fiddle: http://sqlfiddle.com/#!2/4c82d/21

mysql sql database