Paul S Paul S - 1 year ago 78
MySQL Question

MySQL: How to count references to references to a table?

Say I have a database of publishers, who employ authors, who write books.

Or to phrase it another way, each book, is written by an author, who works for a publisher.

publishers: id
authors: id, publisher_id
books: id, author_id

I know how to get a list of publishers with how many authors each employs, from this question.

How do I get a list of publishers with how many books each has published?

How can I get both - publishers, each with number of authors and number of books?

Answer Source

I ended up with something similar, but different to the three answers given:

SELECT publishers.*,
COUNT(authors.publisher_id) AS 'author_count',
COUNT(books.author_id) AS 'book_count'
FROM publishers
LEFT JOIN authors ON (authors.publisher_id =
LEFT JOIN books ON (books.author_id =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download