Hello!

I have three tables, mapping out a n:n relationship of authors and the
books they worked on:

table 1: authors (id, name)
table 2: authorships (author_id, book_id)
table 3: books (id, name, bestseller tinyint)

Here's two different queries I want to run:

1. Select each author, and how many books he has worked on.
2. Select each author, and how many bestseller books (bestseller = 1)
he has worked on.

Not exactly sure how to do this, can someone me out with this?

Thank you,
Rob

Robert MannI wrote:
> Hello!
>
> I have three tables, mapping out a n:n relationship of authors and the
> books they worked on:
>
> table 1: authors (id, name)
> table 2: authorships (author_id, book_id)
> table 3: books (id, name, bestseller tinyint)
>
> Here's two different queries I want to run:
>
> 1. Select each author, and how many books he has worked on.

SELECT a.id, a.name, COUNT(b.id) AS oeuvre FROM authors AS a
LEFT JOIN authorships AS asp ON asp.author_id = a.id
LEFT JOIN books AS b ON asp.book_id = b.id
GROUP BY a.id;

This will also take into account books whose authorship is shared.

> 2. Select each author, and how many bestseller books (bestseller = 1)
> he has worked on.

SELECT a.id, a.name, COUNT(b.id) AS bestsellers FROM authors AS a
LEFT JOIN authorships AS asp ON asp.author_id = a.id
LEFT JOIN books AS b ON asp.book_id = b.id
WHERE b.bestseller = 1
GROUP BY a.id;

Only added the WHERE clause and changed the 3rd column name.

HTH