StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I read your requirements correctly you want a the id of any conversation where the only people in it are (for example) 1,2,3 and 4, and all of those people are in it. If not you want 0 returning for that conversation.</p> <p>If so then something like this</p> <pre><code>SELECT CASE WHEN MatchCount = 4 AND UnMatchCount IS NULL THEN conversations.id ELSE 0 END FROM conversations LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS MatchCount FROM conversations_users WHERE user IN (1,2,3,4) GROUP BY conversation) Sub1 ON conversations.id = Sub1.conversation LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS UnMatchCount FROM conversations_users WHERE user NOT IN (1,2,3,4) GROUP BY conversation) Sub2 ON conversations.id = Sub2.conversation </code></pre> <p>EDIT - Modified version of the above query to only bring back the ids of conversations where there are only those 4 users involved. Playing around this seems to be a pretty efficient way of doing it.</p> <pre><code>SELECT conversations.id FROM conversations LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS MatchCount FROM conversations_users WHERE user IN (1,2,3,4) GROUP BY conversation) Sub1 ON conversations.id = Sub1.conversation LEFT OUTER JOIN (SELECT conversation, COUNT(DISTINCT user) AS UnMatchCount FROM conversations_users WHERE user NOT IN (1,2,3,4) GROUP BY conversation) Sub2 ON conversations.id = Sub2.conversation WHERE MatchCount = 4 AND UnMatchCount IS NULL </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload