CODEWITHSUNDEEP
mysql
ako
ako

Reputation: 2136

Mysql query; get all threads that have all of given users

Database's schema:

A user can participate in multiple threads, and a thread may have multiple users(many-to-many relationship).

Input: An array of user_id's (1,2,3,4...).

Output: Get all the threads(thread_id's) that all given users have participated in(in each thread there must be all given users).

mysql> describe participants;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| thread_id  | int(10) unsigned | NO   |     | NULL    |                |
| user_id    | int(10) unsigned | NO   |     | NULL    |                |
| last_read  | timestamp        | YES  |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| deleted_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

Here is table's schema:

Can anyone help me please with a mysql query to get results?

Upvotes: 1

Views: 128

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can get threads based on your criteria by doing aggregation 

select p.thread_id
from participants p
where p.user_id in(1,2,3,4)
group by p.thread_id
having count(distinct p.user_id) = @count_of_ your_ids /* like 4 */

Upvotes: 1

Related Questions