CODEWITHSUNDEEP
mysqlsqlentity-relationship
vakarami
vakarami

Reputation: 625

Select on many to many relation

I have a two entities Product(int id ID) and Attribute(int id ID). Each Product can have multiple Attributes with custom value, so I have a ProductAttribute(int product_id, int attribute_id, int value).

I want to search for products that have attribute 1 with value x and attribute 2 with value y and ... .

Is that possible? And how? Or should I change the relation map?

I'm using MySQL 5.7 if it matters.

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can use aggregation and having:

select product_id
from ProductAttribute
where (attribute_id, value) in ( (1, 2), (2, 3), (3, 4))
group by product_id
having count(*) = 3;

The "3" is the number of attributes in the list being compared.

Upvotes: 3

Related Questions