SQL IN clause with AND logic in it

sql, sql-server

Solution

In addition to using `IN ()` to merely limit the rowset, you need to verify that the `DISTINCT` set of `CategoryID` per `ItemID`. To ensure that an item is present in both categories, verify that its aggregate `COUNT()` is 2 (equal to the number of items in your `IN ()`).

SELECT
  ItemID
FROM ItemCategories
WHERE CategoryID IN (4,5)
GROUP BY ItemID
-- When there are exactly 2 distinct categories
-- you can be certain that they are the 2 requested in the 
-- IN () clause
-- The value here must be equal to the number of items in the IN ()
HAVING COUNT(DISTINCT CategoryID) = 2

Here is a demonstration: http://sqlfiddle.com/#!6/c9b6c/1

Note: This will supply the set which has exactly that of your `IN ()` clause, and nothing else. If you need to include those which may have other categories not in the `IN ()`, just change it to `>=` instead of `=` in the `HAVING`.

HAVING COUNT(DISTINCT CategoryID) >= 2

Problem

Default IN uses OR base logic. Is there any way to use AND base logic with range. For example in below query ``` SELECT ItemId,CategoryID FROM ItemCategories WHERE CategoryID IN (4,5) ``` One item can have multiple categories. considering following subset as items ``` |ItemID | CategoryID | | 1 | 4 | | 1 | 5 | | 2 | 4 | | 2 | 6 | | 3 | 4 | | 3 | 5 | ``` Is there any way to exclude item2 ? Since Item 2 has category 6. I want items which MUST contain both 4 AND 5. Using IN clause is not helping here. Also 4,5 range is dynamic.

Original source