MYSQL Select message thread id by multiple user ids [duplicate]

Question

I'm putting together a threaded messaging service and I am trying to wrap my head a round a particular query. I am currently trying to create a procedure which will return the thread_id when provided an array of user_ids.

There are only two columns in the table: thread_id & user_id. I have 4 rows in the table for testing:

INSERT INTO thread_users (`thread_id`, `user_id`) VALUES (1,1);
INSERT INTO thread_users (`thread_id`, `user_id`) VALUES (1,70);
INSERT INTO thread_users (`thread_id`, `user_id`) VALUES (2,1);
INSERT INTO thread_users (`thread_id`, `user_id`) VALUES (2,21);

This examples user 1 messaging user 70 to start the first thread and then messages user 21 to start thread 2;

Since I already have the ids of both users what I am trying to derive is finding the the thread_id based on the list of users "subscribed" to the thread.

SELECT participants.*
FROM (
    SELECT DISTINCT `thread_id`
    FROM `thread_users` 
    WHERE `thread_users`.`user_id` IN (1, 21)
    GROUP BY `thread_id`
) AS participants

There is going to be more data requested through a couple of joins, although I don't want to include it into this question... So, with that said, lets just shorten the query to:

SELECT DISTINCT `thread_id`
FROM `thread_participants` 
WHERE `thread_participants`.`user_id` IN (1, 21)
GROUP BY `thread_id`

Of which should return 2.

I recognize that this query should return the proper thread_id wither if there is 2 users associated to a thread or 20. Any help would greatly appreciated:-) Thank You!

Answer 1

if you're just trying to get all participants of a thread_id that has user_id 1 and 21

SELECT participants.user_id
FROM thread_users as participants
INNER JOIN thread_users as my_people
ON my_people.thread_id = participants.thread_id
WHERE my_people.user_id IN (1,21)

if you're trying to get thread_id of your users making sure that they're all participants in the thread. then try this query

SELECT participants.thread_id,COUNT(DISTINCT participants.user_id) as participants
FROM thread_users as participants
WHERE participants.user_id IN (1,21)
GROUP BY participants.thread_id
HAVING COUNT(DISTINCT participants.user_id)=2

the last line just makes sure that your COUNT of DISTINCT user_id is equal to 2 which means your 2 user_id of 1 and 21... If you wanted to find thread of users (1,21,70) you'd replace the (1,21) with (1,21,70) and change that bottom line to HAVING COUNT(DISTINCT participants.user_id)=3 then it would return the thread that has those 3 people as participants.