I am trying to find products that have certain specs. Because of the complex combinations of specs I have another table that is a n:m relation to another table.
product
id | other data ...
---+---------------
1 | bike
2 | bus
3 | car
product_spec
product_id | spec_id
-----------+---------------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 3
2 | 4
3 | 2
3 | 4
Now I need to find a product that has certain specs. This works nicely if I have a list of specs which the product all has to have:
SELECT p.id FROM product p, product_spec s
WHERE p.id = s.product_id AND s.spec_id IN (1, 2, 3)
GROUP BY p.id HAVING COUNT(p.id) = 3
(with 3 being the number of specs that the product has to have) gives product 1
SELECT p.id FROM product p, product_spec s
WHERE p.id = s.product_id AND s.spec_id IN (1, 3)
GROUP BY p.id HAVING COUNT(p.id) = 2
gives product 1 and 2. So far so good. Now comes the trouble:
What if I want to get all products that have spec 1 and have either spec 2 or 4.
SELECT p.id FROM product p, product_spec s
WHERE p.id = s.product_id AND
(s.spec_id = 1 OR s.spec_id IN (2, 4))
GROUP BY p.id HAVING COUNT(p.id) = 2
(clearly) returns product 1 and 3, but 3 erroneously because it does have both spec 2 and 4 but not 1.
I was thinking of giving the different spec clauses values (like spec group 1 = 1, spec group 2 = 2 (spec group 3 = 4, etc)), add them up and use HAVING SUM(extra_field) = 3 but I have been unsuccessful in doing so.
So, how would one do such a query? Do I have to resort to multiple queries (and if, what would be the best approach)?
I am working with MySQL but I assume the question is not restricted to a single system.
Here are the statements to re-create these test tables:
CREATE TABLE product (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(20) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO product (id, title) VALUES (1, 'bike'), (2, 'bus'), (3, 'car');
CREATE TABLE product_spec (
product_id int(10) unsigned NOT NULL,
spec_id int(10) unsigned NOT NULL,
PRIMARY KEY (product_id,spec_id)
);
INSERT INTO product_spec (product_id, spec_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 3), (3, 2), (3, 4);
