how to select rows in a many to many table with multiple filters-CodePudding

I have this table (a table with many to many rows):

 --------- --------- 
| id_user | id_task |
 --------- --------- 
|       1 |       2 |
|       1 |       3 |
|       3 |       1 |
|       3 |       2 |
|       4 |       2 |
|       4 |       5 |
|       5 |       2 |
|       7 |       3 |
|       7 |       5 |
|      10 |       1 |
|      10 |       4 |
|      10 |       5 |
|      11 |       4 |
|      11 |       5 |
 --------- ---------

I need to get only the rows with specific filters. For example, I need only the rows has 3 and 5 id_task minimum (if the user has more tasks is fine, but if the user hasn't the minimum required tasks, doesn't work), the response should be:

 --------- --------- 
| id_user | id_task |
 --------- --------- 
|       7 |       3 |
|       7 |       5 |
 --------- ---------

Or grouped by id_user, the response should be:

 --------- 
| id_user |
 --------- 
|       7 |
 ---------

The simple queries with "AND" & "OR" doesn't work, because get all rows with id_task = 3 and id_task = 5, but I need the records that meet both conditions: users who have tasks 3 and 5 minimum.

PD: The solution will be used on a dinamically filter from HTML form. So the id_tasks will be 2 (3 & 5) or more (2,5,4, etc)

The dump:

-- ----------------------------
-- Table structure for many_many_table
-- ----------------------------
DROP TABLE IF EXISTS `many_many_table`;
CREATE TABLE `many_many_table` (
  `id_user` int(11) NOT NULL,
  `id_task` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of many_many_table
-- ----------------------------
BEGIN;
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (1, 3);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (3, 1);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (3, 2);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (4, 5);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (5, 2);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (10, 1);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (7, 5);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (1, 2);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (4, 2);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (10, 4);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (10, 5);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (11, 4);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (7, 3);
INSERT INTO `many_many_table` (`id_user`, `id_task`) VALUES (11, 5);
COMMIT;

CodePudding user response：

For your exact query, I prefer the following aggregation approach:

SELECT id_user
FROM many_many_table
WHERE id_task IN (3, 5)
GROUP BY id_user
HAVING MIN(id_task) <> MAX(id_task);

A more general form of the above, to which you may add any number of items to the IN (...) clause, would be:

SELECT id_user
FROM many_many_table
WHERE id_task IN (3, 5)  -- add more task IDs here
GROUP BY id_user
HAVING COUNT(DISTINCT id_task) = 2;

CodePudding user response：

How about this:SELECT * FROM many_many_table WHERE id_user = 7 and (id_task = 3 or id_task = 5)

CodePudding user response：

Simple solution is to use inner queries without thinking much

for getting rows matching criteria:

select * from many_many_table where id_user in (select distinct id_user from many_many_table as t1 where id_task = 3 and id_user in (select distinct id_user from many_many_table as t2 where id_task = 5))

you can use group by on this final result to get group by results

CodePudding user response：

I believe you are looking for general approach to the problem. First of all, you need to realize, that you need your data into one 'row', look at this:

id_user	id_task	id_user2	id_task2
7	3	7	5

And this table you can easily make condition. But I would suggest you, to not write query via syntax of joins, but 'joins' the table other way:

SELECT * FROM many_many_table main
WHERE 3 IN (
    SELECT id_task FROM many_many_table WHERE id_user = main.id_user
) AND 5 IN (
    SELECT id_task FROM many_many_table WHERE id_user = main.id_user
);

As this approach is, I believe, way more readable and easily extendable.