Title: SQL SELECT from a many-to-many relationship exactly matching a set of rows
Tags: android;sql;sqlite
Question: I have a SQLite3 database in an Android application with conversations and participants tables.  I want a query that, given a list of participants, returns only those conversations whose participant list is an exact match of the given list of participants.  For example, given these tables:
CONVERSATIONS    CONVERSATION_PARTICIPANTS
-------------    -------------------------
id               conversation_id name
--               --------------- ----
1               1               u1
2               1               u2
3               2               u1
4               3               u1
I expect the following outputs ([participants input] => [conversations output]):
[u1] => [2, 3]
[u2] => []
[u1, u2] => [1]
I have seen solutions that involve concatenating ordered lists of participant names, but I'd rather not go that route if possible.  Is such a thing possible?  Without temporary tables?

Here is the accepted answer: To check whether two sets are not equal, based on individual items, you have to check if there exists, in either set, any item that does not exist in the other set.
Assuming we have a table Input(name), it could be implemented like this:
SELECT id
FROM Conversations
WHERE NOT EXISTS (SELECT 1
FROM Conversation_Participants
WHERE conversation_id = Conversations.id
AND NOT EXISTS (SELECT 1
FROM Input
WHERE name = Conversation_Participants.name))
AND NOT EXISTS (SELECT 1
FROM Input
WHERE NOT EXISTS (SELECT 1
FROM Conversation_Participants
WHERE conversation_id = Conversations.id
AND name = Input.name))
If you do not want to use a (temporary) table for Input, you can replace it with a subquery that returns the values on the fly:
... FROM Input ...
... FROM (SELECT 'u1' AS Name UNION ALL SELECT 'u2') AS Input ...


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:08/04/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<android><sql><sqlite>', 'Title': 'SQL SELECT from a many-to-many relationship exactly matching a set of rows', 'ViewCount': 53, 'id': 25127232, 'user_AboutMe': '', 'user_AccountId': 3857774.0, 'user_DisplayName': 'sjones', 'user_Reputation': 201.0, 'user_Views': 8.0, 'user_id': 3196238.0}}