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

## All we need is an easy explanation of the problem, so here it is.

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!

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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

``````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
``````

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂