For my current project, I have to use a MySQL database for managing a message system which is organized in threads.

The database tables:

User Table: (users)

Participant Table: (participants)
( user-id refers to the ID column of the User table )
( thread refers to the ID column of the Thread table )

Thread Table: (threads)

So for clarifying:

If you have the thread’s ID, you can query the UUIDs of the participating users with

SELECT unique_id 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM participants 
    WHERE thread = [thread ID here] 
);

My question is:
Does anyone know how to reverse this query (=> getting the thread id from an array of multiple unique_ids)

Thanks for your help!

>Solution :

Join the tables and then count the number of participants so it only gets the threads with all of them.

SELECT p.thread
FROM participant AS p ON t.thread = p.thread
JOIN users AS u ON u.id = p.user_id
WHERE u.unique_id IN ('id1', 'id2', 'id3')
GROUP BY p.thread
HAVING COUNT(DISTINCT p.user_id) = 3

Replace 'id1', 'id2', 'id3' with the list of unique IDs you want.