How to query for many, many relationships between products and filters in MySQL?

Question

I have three tables: tb_filters, tb_products and tb_products_to_filters. The structure of these tables together with some dummy data is determined by:

tb_filters:

CREATE TABLE IF NOT EXISTS `tb_filters`
(
    `filter_id` INT (11) AUTO_INCREMENT PRIMARY KEY,
    `filter_name` VARCHAR (255)
);

INSERT INTO `tb_filters`
(`filter_name`)
VALUES ('USB'),
('High Speed'),
('Wireless'),
('Ethernet');

tb_products:

CREATE TABLE IF NOT EXISTS `tb_products`
(
    `product_id` INT (11) AUTO_INCREMENT PRIMARY KEY,
    `product_name` VARCHAR (255)
);

INSERT INTO `tb_products`
(`product_name`)
VALUES ('Ohm precision shunt resistor'),
('Orchestrator Libraries'),
('5cm scanner connection'),
('Channel isolated digital'),
('Network Interface Module');

tb_products_to_filters:

CREATE TABLE IF NOT EXISTS `tb_products_to_filters`
(
    `id` INT (11) AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT (11),
    `filter_id` INT (11)
);

INSERT INTO `tb_products_to_filters`
(`product_id`, `filter_id`)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 3),
(1, 3);

Studying the tb_products_to_filters table above, my required queries are:

When the filter id = 1 and 3 are selected using the checkbox on the page, all those products that belong to the filter ID 1, as well as the filter ID 3, must be retrieved from the database. In this case, the product with id 1 should come.

Secondly, when only one filter is checked (for example, id = 3), all products that fall under this identifier must be retrieved. In this condition, products id 1, 3 and 4 will be displayed.

2, id = 2.

(2 3), , , .

?

, : product_id, product_name, filter_id _, .

EDIT:

, 1 3:

2:

1 3:

SELECT `p`.`product_id`, `p`.`product_name`, 
GROUP_CONCAT(DISTINCT `f`.`filter_id` ORDER BY `f`.`filter_id` SEPARATOR ', ') AS filter_id, GROUP_CONCAT(DISTINCT `f`.`filter_name` ORDER BY `f`.`filter_name` SEPARATOR ', ') AS filter_name 
FROM `tb_products` AS `p` INNER JOIN `tb_products_to_filters` AS `ptf` 
ON `p`.`product_id` = `ptf`.`product_id` INNER JOIN `tb_filters` AS `f` 
ON `ptf`.`filter_id` = `f`.`filter_id` GROUP BY `p`.`product_id` 
HAVING GROUP_CONCAT(DISTINCT `ptf`.`filter_id` SEPARATOR ', ') = ('1,3') 
ORDER BY `p`.`product_id`

, , . ?

Answer 1

HAVING GROUP_CONCAT:

SELECT t.product_id,tp.product_name,
       GROUP_CONCAT(t.filter_id) as filter_id,
       GROUP_CONCAT(tb.filter_name) as filter_name
FROM tb_products_to_filters t
INNER JOIN tb_filters tb ON(t.filter_id = tb.filter_id)
INNER JOIN tb_products tp ON(t.product_id = tp.product_id)
WHERE t.filter_id IN(1,3)
GROUP BY t.product_id
HAVING COUNT(distinct t.filter_id) = 2

, . , , IN(), , COUNT(..) = X

EDIT:

DISTINCT GROUP_CONCAT , . ,

SELECT t.product_id,tp.product_name,
       GROUP_CONCAT(DISTINCT t.filter_id ORDER BY `t`.`filter_id` SEPARATOR ', ') as filter_id,
       GROUP_CONCAT(DISTINCT tb.filter_name ORDER BY tb.filter_name SEPARATOR ', ') as filter_name
FROM tb_products_to_filters t
INNER JOIN tb_filters tb ON(t.filter_id = tb.filter_id)
INNER JOIN tb_products tp ON(t.product_id = tp.product_id)
WHERE t.filter_id IN(1,3)
GROUP BY t.product_id
HAVING COUNT(distinct t.filter_id) = 2

(Ethernet, High Speed, USB, Wireless) . , (1, 3) ?