Given the following tables I would like to know how to write a query to return only the categories that have books in them and the number of books for each category. A book can be added in one or many categories. I'm using PHP and MySQL.
Here are my tables:
categories table
- id
- name
- permalink
books table
- id
- title
- author
- description
- price
books_categories table
- id
- book_id
- category_id
select c.id
,c.name
,count(*) as num_books
from categories c
join books_categories bc on(bc.category_id = c.id)
group
by c.id
,c.name;
Use LEFT JOIN if you also want the categories without books.
SELECT *, COUNT(*) AS count
FROM books_categories AS bc
LEFT JOIN categories AS c ON c.id = bc.category_id
GROUP BY c.id
You'll get a count column with the number of rows for each category. Categories with zero books (that is those with no entry in table books_categories) won't be returned.
SELECT categories.id,
COUNT(books_categories.id) AS number
FROM categories
LEFT JOIN books_categories ON books_categories.category_id = categories.id
GROUP BY categories.id
HAVING number > 0;
