CODEWITHSUNDEEP
mysqlforeign-keys
Paul S
Paul S

Reputation: 250

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?

Upvotes: 0

Views: 977

Answers (4)

Paul S
Paul S

Reputation: 250

I ended up with something similar to bhanu's answer:

SELECT publishers.*,
COUNT(DISTINCT authors.id) AS 'author_count',
COUNT(DISTINCT books.id) AS 'book_count'
FROM publishers
LEFT JOIN authors ON (authors.publisher_id = publishers.id)
LEFT JOIN books ON (books.author_id = authors.id)
GROUP BY publishers.id;

Upvotes: 0

bhanu avinash
bhanu avinash

Reputation: 482

try this 

SELECT COUNT(DISTINCT b.`id`) noofbooks,COUNT(DISTINCT au.id) noofauthers,pub.id publisher FROM publisher pub
    INNER JOIN auther au ON au.`pub_id`= pub.`id`
    INNER JOIN books b ON b.`aut_id` = au.`id` GROUP BY pub.id

Upvotes: 2

kaustubh
kaustubh

Reputation: 178

You just need to fire a simple sql join query for that like as follow.

    SELECT p.publishers , COUNT(a.authors) totalAuthors, COUNT(b.books) TotalBooks
    FROM publishers AS p,authors AS a ,books AS b
    WHERE p.publishersid = a.publishersid
    AND a.authorsid = b.authorsid
    GROUP BY p.publishersid;

Upvotes: 1

e4c5
e4c5

Reputation: 53774

You need a three table join

SELECT publisher.id, count(*) from publisher 
INNER JOIN author on publisher.id = author.publisher_id 
INNER JOIN book on author.id = book.author_id GROUP BY publisher.id;

Upvotes: 1

Related Questions