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.

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply