Question

I try to create a filter that is based on attributes. It is envisaged that this is a multi-filter.

我的桌子看起来是这样的:

product_id  attribute_id  attribute_option_id
----------  ------------  -------------------
4           16            51
4           13            28
5           16            51
6           16            51

If you choose attribute_option_id 51 then i will get product_id 4, 5 and 6. When i choose the attribute_option_id(s) 51 and 28 i wil get the product_id(s) 4, 5 and 6. This a result that don t want. In this case i would only want product_id 4. Now I thought that I could solve this by this line

WHERE product_attribute.Attribute_option_id IN ( 51  ,  28 )

但预计这不是解决办法。

有人能帮我吗?

Answer 1

select a.product_id from (
  select product_id, count(*) as cnt 
  from mytable
  where attribute_option_id in (51, 28)
  group by product_id having count(*) =2) a

应该做这个把戏。

Answer 2

SELECT * 
FROM   mytable 
WHERE  product_id IN (SELECT a.product_id 
                      FROM   mytable a 
                             INNER JOIN mytable b 
                               ON a.product_id = b.product_id 
                      WHERE  a.attribute_option_id =  51  
                             AND b.attribute_option_id =  28 )

友情链接