Php Sql Select Where In Array Of Ids And Has Same Variable Column Value

So I have a table kind of like the one below. +----+----------+---------+ | ID | group_id | user_id | +----+----------+---------+ | 1 | 10 | 9 | | 2 | 20 |

Solution 1:

You can use conditional aggregation to find group_id's which have the required user_id's associated with them.

SELECT group_id 
FROM group_user 
groupby group_id
havingsum(casewhen user_id IN (9,7,6) then1else0end) =3

Solution 2:

Your query should be:

SELECTDISTINCT(group_id) FROM group_user WHERE user_id IN (9,7,6)

Solution 3:

SELECT group_id 
FROM group_user 
WHERE user_id IN (9,7,6) 
GROUPBY group_id
HAVINGcount(DISTINCT user_id) =3ANDcount(DISTINCT group_id) =1

makes sure that all 3 ids are in the same group_id and that they're not in any other group.

http://sqlfiddle.com/#!9/540f96/2