MySQL: Find ID with only 2 attached IDs in subtable I've got 2 tables. 1 containing my Threads and 1 containing the users attached to these threads.

I want to find the ThreadID with 2 specific UserIDs attached, no more no less.

This is what I've come up with:

SELECT mt.ThreadID 
FROM MessagesThreads AS mt
WHERE (SELECT COUNT(DISTINCT mtau.UserID)
       FROM MessagesThreadsAccessUsers AS mtau
       WHERE
        mtau.ThreadID = mt.ThreadID
        AND EXISTS (
         SELECT 0
         FROM MessagesThreadsAccessUsers AS mtau2
         WHERE mtau2.ThreadID = mtau.ThreadID
         AND mtau2.UserID = 1
        )
        AND EXISTS (
         SELECT 0
         FROM MessagesThreadsAccessUsers AS mtau2
         WHERE mtau2.ThreadID = mtau.ThreadID
         AND mtau2.UserID = 2
        )
       ) = 2


This specific situation is about finding the ThreadID with UserID 1 and UserID 2 attached.
If no ThreadID is found, I need to create a new one further down the path.

My question is: Is this the best way to query this or can it be more intelligent?
You've tagged your question with "t-sql", so here goes (although I think/hope that this should be ANSI SQL compliant) :

SELECT DISTINCT mt.ThreadID
FROM MessageThreads AS mt
INNER JOIN MessageThreadAccessUsers AS mtau1 ON mtau1.ThreadID=mt.ThreadID AND mtau1.UserID=1
INNER JOIN MessageThreadAccessUsers AS mtau2 ON mtau2.ThreadID=mt.ThreadID AND mtau2.UserID=2
LEFT JOIN  MessageThreadAccessUsers AS mtau3 ON mtau3.ThreadID=mt.ThreadID AND mtau3.UserID NOT IN (1, 2)
WHERE mtau3.ThreadID IS NULL;


This is probably more efficient than a GROUP BY with conditional SUM()s, particularly so if you have an index on MessageThreadAccessUsers that covers the columns (ThreadID, UserID). If users are distinct for each thread, you can even remove DISTINCT.

Edit: Added LEFT JOIN and WHERE .. IS NULL to exclude any other users.