SQL IN clause with AND logic in it

Question

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.

Answer 1

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