Find products that have a list of attributes
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 | Attr开发者_Python百科ibute_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.
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.
select product_id from products
where attribute_id in (123,234)
group by product_id having count(*) = 2
select products_id from table where attribute_id = 456
intersect
select products_id from table where attribute_id = 26;
精彩评论