How can I run this SELECT query?

Question

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.

Gordon Linoff · Accepted Answer · 2020-02-23T03:39:52.087

1

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

edited Feb 23 '20 at 03:39

answered Feb 22 '20 at 23:00

Gordon Linoff

1,242,037
58
646
786

This seems simpler than the other answer, but this also selects cases where additional ids are included, e.g. (1,3,5,7,9), (1,3,5,7,9,11), etc. However your answer did help me find a duplicate [here](https://stackoverflow.com/questions/12776178/sql-select-sets-containing-exactly-given-members). With the info there, I've set up the following: SELECT tp.threadId FROM dbo.ThreadParticipant tp WHERE tp.userId IN (2, 25, 48) GROUP BY tp.threadId HAVING COUNT(*)=( SELECT COUNT(*) FROM ThreadParticipant tp2 WHERE tp.threadId = tp2.threadId GROUP BY tp2.threadId ) AND Count(*)=3; – cweber105 Feb 23 '20 at 00:09
1

@cweber105 . . That was how I interpreted your question. I edited the answer based on your comment. – Gordon Linoff Feb 23 '20 at 03:40

GMB · Answer 2 · 2020-02-22T23:12:10.557

0

You can use exists:

select 
    t.*,
    case 
        when exists (
            select 1 
            from threadParticipant p 
            where p.threadId = t.id and p.userid not in (1, 3, 5, 7)
        ) 
        then 'not only these users'
        else 'these users only'
    end status
from thread t

The status columns indicates whether or not the thread contains a user other than 1, 3, 5, 7.

edited Feb 22 '20 at 23:12

answered Feb 22 '20 at 23:02

GMB

216,147
25
84
135

How can I run this SELECT query?

2 Answers2