I have a single table containing nothing but productID and colorID. There are 10,000 rows in the table.
I need to be able to select all productID having colorID 2 and colorID 5. For example:
productID colorID 1 1 2 9 3 2 4 2 3 6 3 5 1 5
Using this data set I am looking for productID 3, since it is the only productID that has colorID 2 AND colorID 5 (it also has colorID 6, but I don't care about that)
WHERE colorID=2 AND colorID=6 returns 0 rows.
WHERE colorID IN (2,6) returns values for products that do not have both colorID 2 and 6.
This seems like a simple thing to query, but I am finding myself up against a wall. What's the best way to accomplish this in a reasonably efficient way?