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?

  • WHERE is applied to one row at a time. You need something more complex to look at multiple rows at the same time.
    – Rick James
    Mar 1, 2018 at 20:12

4 Answers 4


If what you want is to find ProductID that has both 2 and 6 values, try below query:

-- Assuming table is named #tmp

FROM #tmp a 
        COUNT(DISTINCT ColorID) AS cnt
    FROM #tmp
    WHERE ColorID IN (2, 6)
    GROUP BY ProductID
    HAVING COUNT(DISTINCT ColorID) = 2) AS b ON a.ProductID = b.ProductID
WHERE a.ColorID IN (2, 6)

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 ;

i needed a solution for the same problem recently. i wrote this PHP script:

$c1 = array_shift($colors); // first color //
$sql = "select pc_$c1.productId
  from product_color as pc_$c1";
foreach ($colors as $c) { // more colors, if any //
  $sql .= "
    inner join product_color as pc_$c on pc_$c.productId = pc_$c1.productId";
$sql .= "
  where pc_$c1.colorId = '$c1'"; // first color //
foreach ($colors as $c) { // more colors, if any //
  $sql .= "
    and pc_$c.colorId = '$c'";

assuming that the table name is "product_color" and the $colors array contains the elements (2, 5), the script generates the following MySQL query:

select pc_2.productId
  from product_color as pc_2
    inner join product_color as pc_5 on pc_5.productId = pc_2.productId
  where pc_2.colorId = '2'
    and pc_5.colorId = '5'

the $colors array must not be empty and must be distinct (must not have an element multiple times). the script above will generate a well-formed query for even longer input lists.


I created a table named "colors". I'm assuming what you are looking for are single productIDs that have both colorID = 2 and 5.

I think there should be a simpler way but this is what I've done.

SELECT productID
FROM colors
WHERE productID IN 
    FROM colors
    WHERE colorID = 2)
    AND colorID = 5;

The inner subquery produces a list of productIDs that have colorID =2 (and also could have other colorIDs). The outer query re-checks the list for productIDs which also have colorID = 5. It's important to use "IN" not "=" in the outer query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.