如何在MySQL中查询产品和过滤器之间的多对多关系？

Question

I have three tables viz. tb_filters, tb_products, and tb_products_to_filters. The structure of these tables along with some dummy data is given 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);

By looking into above "tb_products_to_filters" table, my required queries are:

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

Second, when only one filter (say id = 3) is checked, then all those products which fall under this id should be fetched. In this condition, the products id 1, 3 and 4 will come.

If filter id 2 is selected, then only one product with id = 2 will come.

If combination of filter (2 and 3) is selected, then no product will come because there is no product which belongs to both of them.

What is the way of writing queries to obtain above goal?

Please note that I want to include columns: product_id, product_name, filter_id and filter_name to display data in table result set.

EDIT:

The output should match below when filter ids 1 and 3 were checked:

EDIT 2:

I'm trying below query to fetch results when filter 1 and 3 were checked:

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`

But unfortunately, it returns an empty set. Why?

Answer 1

You can use the HAVING clause with 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

You can adjust this any way you want. Note that the number of arguments placed inside the IN() should be the same as the COUNT(..) = X

EDIT:

A DISTINCT keyword is required in GROUP_CONCAT while fetching those columns otherwise all the filters would come in the list. I tried it by doing

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

But still all the filter names (Ethernet, High Speed, USB, Wireless) are coming in the list. How to list only those filter names whose corresponding filter id (1, 3) are in the string?