Getting all shared values of two or more given values in one table (SQL)

Question

I have a chat database where each user belongs to a chat; this relation is represented in the table "is_member(uid, cid)". Now, when creating a new chat, I want to check if there is not already a chat with exactly these users. Here is my example table (users 1, 2, 3 and users 2, 3 have a chat together):

uid | cid
----|----
 1  |  1
 2  |  1
 3  |  1
 2  |  2
 3  |  2

Now how can I formulate my (My)SQL statement to get e.g. with the parameters uid=2 and uid=3 as output the following:

cid
---
 1
 2

… or with the parameters uid=1, uid=2 and uid=3:

cid
---
 1

Your help is much appreciated, I'm relatively new to SQL and I found nothing similar in this regard anywhere online. Thanks in advance!

Answer 1

Based on the data and expected result I think you are looking for MySQL HAVING clause.

Your data:

CREATE TABLE test(
uid INT,
cid int  );

insert into test values (1,1), (2,1),(3,1),(2,2),(3,2);

First query.

Now how can I formulate my (My)SQL statement to get e.g. with the parameters uid=2 and uid=3 as output the following:

select cid
from test
where uid in (2,3)
group by cid
having count(distinct uid) =2;

Result:

cid
1
2

Second query.

or with the parameters uid=1, uid=2 and uid=3:

select cid
from test
where uid in (1,2,3)
group by cid
having count(distinct uid) =3;

Result:

cid
1

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d98d0132ecea7b4730ab7b8e70813817

Note. The query is not dynamic so each time you change the where clause you need to change the count(distinct uid) to the number of values used on the where condition