Using IN with ALL

Question

How can I select the book Id that is listed under every category id I provide as a set of category Ids

e.g I want to get the book Ids that are listed under all categories I listed here: (A,B,C,D,...etc) and not that are listed under any of them.

the following select statement does not work with my query's conditions: SELECT bookId FROM bookCategories WHERE categoryId IN ('A','B','C','D',.....); because it retrieves the books that are listed under any of the specified categories.

Thanks for any help..

Answer 1

you can use IN & also check that the number of distinct categories for each book is the same as the number of categories you supplied in your IN predicate:

SELECT T.bookId
  FROM (SELECT bookId, count(distinct categoryId) catCount
          FROM bookCategories
         WHERE categoryId IN ('A','B','C','D',.....)
      GROUP BY bookId) T
 WHERE T.catCount = myCategoriesCount

If you don't know how many categories you have, you can create a temp table #CAT(categoryId), populate it with your categories and run the folowing query:

SELECT T.bookId
  FROM (SELECT bc.bookId, count(distinct c2.categoryId) catCount
          FROM bookCategories bc
          JOIN #CAT c2 on bc.categoryId = c2.categoryId
      GROUP BY bc.bookId) T
WHERE T.catCount = (SELECT COUNT(DISTINCT categoryId) FROM #CAT)