Find products that have a list of attributes.

Question

I'm stuck trying to solve a problem that's proving to be more difficult than it seems.

Consider there is a table that associates products with attributes, it looks like this:

Products_id | Attribute_id
    21      |      456
    21      |      231
    21      |      26
    22      |      456
    22      |      26
    22      |      116
    23      |      116
    23      |      231

Next, I have a list of attribute_ids which I want to use in order to get the products that have all the attributes in that list.

For example if I search in the table above using this list (456, 26) I should get these product_ids 21 and 22. Another example, if I search for (116, 231) I should get 23 since the product 23 is the only on that has both these attributes.

How can I achieve this using one query?

I hope I made my question clear.

Thanks.

Answer 1

select product_id from products 
where attribute_id in (123,234) 
group by product_id having count(*) = 2

Answer 2

If you hand-craft your query:

SELECT Products_ID
  FROM ProductAttributes
 WHERE Attribute_ID IN (116, 231)
 GROUP BY Products_ID
HAVING COUNT(*) = 2;

This asks for all the products where the number of entries with the given attributes is equal to the number of attributes. If you want to generalize, you do a join against a table of the interesting product attribute ID values.

Answer 3

select products_id from table where attribute_id = 456
intersect
select products_id from table where attribute_id = 26;