CODEWITHSUNDEEP
mysql
As SV
As SV

Reputation: 1

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

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;

Upvotes: 0

Views: 94

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

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;

Upvotes: 2

Rajani B
Rajani B

Reputation: 203

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

Upvotes: 0

Kazakh
Kazakh

Reputation: 104

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

Upvotes: 0

Related Questions