Count statement one to many relationship with where

I'm trying to do a count statement for a database. Each book can be in multiple categories (eg; horror and fiction), so I tried the statement below, but I just get 0 when I try to get the second category. Any assistance would be greatly appreciated.

SELECT count(*) a开发者_JS百科s count FROM book
Inner JOIN bookscategories ON book.bookid = bookscategories.bookid
WHERE bookscategories.categoryid = 3 AND bookscategories.categoryid = 4

---

The categoryid can't be set to both 3 and 4 at the same time, which is what you're testing for when you use AND. You'll probably need to use OR instead, or alternatively you could use an IN clause:

WHERE bookcategories.categoryid = 3 OR bookcategories.categoryid = 4

... or ...

WHERE bookcategories.categoryid IN (3, 4)

---

This sql is assuming that you want to find the number of books that are in both category 3 and category 4. The other answers do an in cat 3 or cat 4

SELECT count(*) as count FROM book 
Inner JOIN bookscategories bc ON b.bookid = bc.bookid
Inner JOIN bookscategories bc2 ON book.bookid = bc2.bookid
WHERE bc.categoryid = 3 AND bc2.categoryid = 4

---

You have to understand that SQL works with the cartesian products of tables.

In your query, you'll have

book1   book1cat1
book1   book1cat2

You are asking that, for each record, book1cat1 is both 3 AND 4. It is impossible as it can only be one of them.

---

If you want the count for each category:

SELECT bookscategories.categoryid, count(book.bookid) as count
  FROM book
 Inner JOIN bookscategories ON book.bookid = bookscategories.bookid
                           AND bookscategories.categoryid in (3, 4)
 GROUP BY bookscategories.categoryid