Database architecture for filtering products - how to select by several properties at once?

Question

MySQL

Hello colleagues!
I'm trying to organize the storage of goods and their properties in MySQL. And at the same time be able to filter products by property values.
Created four tables
`article` - id, name
+----+------------------+
| id | name |
+----+------------------+
| 1 | T-shirt |
| 2 | T-shirt |
+----+------------------+
`prop` - id, name
+----+------------ --+
| id | name |
+----+------+
| 1 | Color |
| 2 | Size |
+----+--------------+
`prop_value` - id, name
+----+---------------- +
| id | value |
+----+----------------+
| 1 | White |
| 2 | Red |
| 3 | Big |
+----+----------------+
`prop2article` - id, article_id, prop_id, prop_value_id
+----+---------+ ---------------+------------+
| id | prop_id | prop_value_id | article_id |
+----+---------+---------------+------------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 |
| 3 | 2 | 3 | 1 |
+----+---------+---------------+------------+
Now I'm trying to select a product immediately by two properties - Color (1) = White (1) and Size (2) = Large (3):

SELECT DISTINCT article.name
FROM article
INNER JOIN prop2article ON article.id = prop2article.article_id
WHERE
  (prop2article.prop_id = 1 and prop2article.prop_value_id = 1)
  AND
  (prop2article.prop_id = 2 and prop2article.prop_value_id = 3)

And in the end, the request returns nothing. What am I doing wrong?
Thanks in advance for your help!

Answer 1

SELECT DISTINCT article.name
FROM article
INNER JOIN prop2article P1 ON article.id = P1.article_id
INNER JOIN prop2article P2 ON article.id = P2.article_id
WHERE
  (P1.prop_id = 1 and P1.prop_value_id = 1)
  AND
  (P2.prop_id = 2 and P2.prop_value_id = 3)

Answer 2

Usage error in WHERE clause:

WHERE
  (prop2article.prop_id = 1 and prop2article.prop_value_id = 1)
  AND
  (prop2article.prop_id = 2 and prop2article.prop_value_id = 3)

Need

WHERE
  (prop2article.prop_id = 1 and prop2article.prop_value_id = 1)
  OR
  (prop2article.prop_id = 2 and prop2article.prop_value_id = 3)

Through INNER JOIN you got these records:
| article.id | article.name | prop2article.id | prop2article.prop_id | prop2article.prop_value_id | prop2article.article_id |
| 1 | T-shirt | 1 | 1 | 1 | 1 |
| 1 | T-shirt | 2 | 1 | 2 | 1 |
| 1 | T-shirt | 3 | 2 | 3 | 1 |
…
Which of these entries have both color and size? There is only such and such a color or such and such a size.