Select rows from table having two specific values

Question

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?

Answer 1

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

-- Assuming table is named #tmp

SELECT
    a.*
FROM #tmp a 
INNER JOIN (
    SELECT
        ProductID,
        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)

Answer 2

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 ;

Answer 3

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.

Answer 4

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 
    (SELECT DISTINCT(productID)
    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.