CODEWITHSUNDEEP
mysql
Psyche
Psyche

Reputation: 8773

Help with a MySQL query

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

Upvotes: 1

Views: 101

Answers (3)

vbence
vbence

Reputation: 20333

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;

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

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.

Upvotes: 5

Czechnology
Czechnology

Reputation: 14992

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.

Upvotes: 2

Related Questions