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.
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);
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;
cid 1 2
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;
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 🙂