Select rows from table having two specific values 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)

Using WHERE colorID=2 AND colorID=6 returns 0 rows.

Using 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?
You can also use a simple self-join. If there is no UNIQUE constraint on (ColorID, ProductID), then use SELECT DISTINCT:

SELECT productID
FROM tbl AS a
  JOIN tbl AS b
  USING (productID)
WHERE a.colorID = 2
  AND b.colorID = 6 ;