PHP SQL select where in array of IDs and has same

Question

This question already has answers here:

How to return rows that have the same column values in MySql (3 answers)

Closed 4 years ago.

So I have a table kind of like the one below.

+----+----------+---------+
| ID | group_id | user_id |
+----+----------+---------+
|  1 |       10 |       9 |
|  2 |       20 |       8 |
|  3 |       10 |       7 |
|  4 |       10 |       6 |
|  5 |       20 |       5 |
+----+----------+---------+

I have an array of user IDs [9,7,6] and I'd like to get the Group ID where all the IDs are in the same group?

My current query:

SELECT group_id FROM group_user WHERE user_id IN (9,7,6) GROUP BY group_id

Bare in mind I'm not overly familiar with SQL queries.

Thanks

---

EDIT

I have opened a new question with more detail and my goal.

SQL, check if conversation exists between group

---

Answer 1

SELECT group_id 
FROM group_user 
WHERE user_id IN (9,7,6) 
GROUP BY group_id
HAVING count(DISTINCT user_id) = 3
AND count(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

Answer 2

Your query should be:

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

Answer 3

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 
group by group_id
having sum(case when user_id IN (9,7,6) then 1 else 0 end) = 3