Styjun

I would like to select products from products table with the attributes with id 2 and 5 using the following query:

SELECT `products`.`title`, `products`.`price` 
FROM `products` 
LEFT JOIN `products_attributes_mapping` 
 ON `products`.`id` = `products_attributes_mapping`.`product_id` 
WHERE 
 `products_attributes_mapping`.`attribute_value_id` IN (2) 
 AND `products_attributes_mapping`.`attribute_value_id` IN (5) 
GROUP BY `products`.`id`

I expect the product 'Example product 1, blue, size 1' to be returned. But I don't get any result, even though the product with id 1 has attribute_value_id 2 and 5 assigned in the products_attributes_mapping table.

I use IN because I would like to be able to provide multiple attributes, I simplified it only for the example.

SQL fiddle: http://sqlfiddle.com/#!9/2fd94f2/1/0

Schema

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) CHARACTER SET utf8 NOT NULL,
 `price` double NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `products_attributes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `products_attributes_mapping` (
 `product_id` int(11) NOT NULL,
 `attribute_value_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `products_attributes_values` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `attribute_id` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

Data

INSERT INTO `products` VALUES 
 (1,'Example product 1, blue, size 1',10),
 (2,'Example product 2, yellow, size 1',10),
 (3,'Example product 3, black, size 2',15),
 (4,'Example product 4, red, size 2',15);

INSERT INTO `products_attributes` VALUES 
 (1,'Color'),
 (2,'Size');

INSERT INTO `products_attributes_mapping` VALUES 
 (1,2),
 (1,5),
 (2,4),
 (2,5),
 (3,3),
 (3,6),
 (4,1),
 (4,6);

INSERT INTO `products_attributes_values` VALUES 
 (1,1,'red'),
 (2,1,'blue'),
 (3,1,'black'),
 (4,1,'yellow'),
 (5,2,'1'),
 (6,2,'2'),
 (7,2,'3'),
 (8,2,'4');

Using aggregation could indeed be a solution. You can use a HAVING clause to ensure that a products has certain attribute values:

SELECT p.title, p.price
FROM products p
INNER JOIN products_attributes_mapping pm ON p.id = pm.product_id 
GROUP BY p.id, p.title, p.price
HAVING 
 MAX(pm.attribute_value_id = 2) = 1
 AND MAX(pm.attribute_value_id = 5) = 1

In your DB fiddle, this query returns:

title | price
---------------------------------|-------
Example product 1, blue, size 1 | 10

You can easily extend the expression by adding more AND MAX(...) = 1 conditions.

Another option would be to use a series of WHERE EXISTS conditions with correlated subqueries to search the attributes tables. This is just as good, but will expand as a longer query if you need to add many conditions.