mysql - 产品属性选择

我有三个数据库表：

products
(
  product_id, name etc..
)

specifications
(
  id_specification, name 
)

product_has_specification
(
  id_specification, product_id, specification_value
)

假设我有一件T恤（product_id = 1），大小M的颜色为蓝色，属性更多 .

我已经尝试加入这三个表并且值= m AND value = blue等 . 但它不显示任何结果 .

我也试过像这样的子查询：

select distinct products.* from products
join product_has_specification on products.id = product_has_specification.product_id
join specifications on product_has_specification.spec_id = pecifications.id_specification
where
(
(specifications.name = 'color' and product_has_specification.value='black')

)
 and products.id in 
 (
select products.id from products
left join product_has_specification on products.id = product_has_specification.product_id
left join specifications on product_has_specification.spec_id = specifications.id_specification
where

(specifications.name='size' and product_has_specification.value='s')

 )

如果产品的颜色为“黑色”且尺寸为“s”，则此选择有效;但如果我有更多属性，则说X选择将太长 .

选择怎么样？还有其他解决方案吗？

编辑：

我找到了解决方案 . 它很难看，但它能完成这项工作 . 但是，如果一个产品，让我们说10个规格的查询很长 .

SELECT products.*
        FROM products
        left JOIN product_has_specification ON products.id = product_has_specification.product_id
        left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
        WHERE products.id in (

                SELECT products.id
                FROM products
                left JOIN product_has_specification ON products.id = product_has_specification.product_id
                left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
                where   (product_has_specification.value = 'm') and products.id in 

                    (
                        SELECT products.id
                        FROM products
                        left JOIN product_has_specification ON products.id = product_has_specification.product_id
                        left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
                        where   (product_has_specification.value = 'black') and products.id in 
                        (
                                SELECT products.id
                                FROM products
                                left JOIN product_has_specification ON products.id = product_has_specification.product_id
                                left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
                                where   (product_has_specification.value = 'test') 
                        )

                    )

        )
        group by products.id

使此查询适应您想要的任意数量的规范 . 如果有人有更好的解决方案，请发布 .