How can I run this SELECT query?

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.

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

Advertisement

Answer