Using IN with ALL
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 d开发者_运维问答oes 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..
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)
精彩评论