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 ;

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.