2

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!

5
  • Search for "(exact) relational division" Jun 12 at 11:52
  • In your first example, if you query for uid=2 and uid=3 should both cid=1 and cid=2 be returned? Or just cid=2? You wording says "exactly" (so exact division) but if the expected is both cids, then you want plain relational division, not exact. Jun 12 at 11:57
  • Hi, and welcome to dba.se! Have you considered a UNIQUE index on (uid, cid)? Jun 12 at 12:33
  • @ypercubeᵀᴹ I looked into relational divison (geeksforgeeks.org/sql-division) and it's not exactly what I'm looking for. For my example above (if I use the terms from the article), R(uid, cid) and S(cid), gives me a list of all users who are ever in all chats (2 and 3). But I have an input! I want to determine if a given "list" of users has a common chat (if every user from the list has at some point the same entry in cid).
    – Hape
    Jun 12 at 16:18
  • Sounds like a "many-to-many" mapping table. Here's the optimal schema: Many-to-many
    – Rick James
    Jun 21 at 4:23

1 Answer 1

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

1
  • Thank you very much, I was very close to this solution but I didn't knew about the "IN (1, 2, 3)"-thing! I chose in the meantime another approach where I format a broader SQL request with PHP.
    – Hape
    Jun 13 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.