I have a many to many table setup in my mysql database, with products and attributes. A product can have many attributes and attributes can be assigned to many products.
Now I'm stuck for a while where I need to get some products which have some attributes assigned to it but don't have some other attributes assigned. It's a search question like:
"Dog food with salmon or chicken and without grain"
In this example the product must have the attributes Dog and Food, it can have Salmon or Chicken, and it must not have grain.
table : product_attributes
+------------+----------------+
| product | attribute |
+------------+----------------+
| Product 1 | Dog |
| Product 2 | Cat |
| Product 3 | Dog |
| Product 1 | Food |
| Product 2 | Food |
| Product 3 | Food |
| Product 1 | Salmon |
| Product 2 | Chicken |
| Product 3 | Salmon |
| Product 1 | Chicken |
| Product 3 | Grain |
| Product 4 | Dog |
| Product 4 | Food |
| Product 4 | Cow |
+------------+----------------+
If we pick up the search question, then I only want product 1 to show because it matches with Dog, Food and Salmon / Chicken and it doesn't have the grain attribute. Product 2 doesn't match the Dog attribute and Product 3 has the grain attribute we don't want.
+------------+
| product |
+------------+
| Product 1 |
+------------+
Selecting only the really 'and' relations Dog & Food are no problem, but I'm stuck when I need to get the 'or' relation and 'not' relation in the query
select product
from product_attribute
where attribute in ('dog', 'food')
group by product
having count( attribute ) = 2
+------------+
| product |
+------------+
| Product 1 |
+------------+
A simple sql fiddle with some data can be found here: http://www.sqlfiddle.com/#!9/e2ed09/4
Best Regards,
Martijn Bastiaansen
You are almost there, just use not exists
to exclude the products you do not want and exists
to solve the OR
select product
from product_attribute
where attribute in ('dog', 'food') and
not exists(
select 1
from product_attribute pa2
where pa2.product = product_attribute.product and
pa2.attribute in ('grain')
) and
exists(
select 1
from product_attribute pa2
where pa2.product = product_attribute.product and
pa2.attribute in ('salmon', 'chicken')
)
group by product
having count(distinct attribute ) = 2