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 '18 at 20:12

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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