I’m working on a Messaging feature for an application, and I have a the following tables in MSSQL, along with a User table (not listed):
CREATE TABLE Thread ( id int IDENTITY(1,1), createdAt datetimeoffset NOT NULL, CONSTRAINT PK_Thread_id PRIMARY KEY (id) ) GO CREATE TABLE ThreadParticipant ( userId int NOT NULL, threadId int NOT NULL, createdAt datetimeoffset NOT NULL, CONSTRAINT PK_userId_threadId PRIMARY KEY (userId, threadId), CONSTRAINT FK_ThreadParticipant_userId FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE, CONSTRAINT FK_ThreadParticipant_threadId FOREIGN KEY (threadId) REFERENCES Thread(id) ON DELETE CASCADE ) GO
I want to query the ThreadParticipant
table for whether or not a thread exists containing only a given set of user ids, let’s say users 1,3,5,7
. Each Thread
contains a unique set of user ids. How could I query for this data? If it’s any simpler, I can put some of the logic in the server (node.js). I’ve looked for question duplicates online and can’t find any, though I’m not sure exactly how to phrase the question to locate them if they’re around.
Advertisement
Answer
You can use aggregation with a having
clause:
select tp.threadid from ThreadParticipant tp where tp.userid in (1, 3, 5, 7) group by tp.threadid having count(*) = 4;
EDIT:
If you want ONLY these users, then use:
select tp.threadid from ThreadParticipant tp group by tp.threadid having sum(case when tp.userid in (1, 3, 5, 7) then 1 else 0 end) = 4