herbasher wrote:
> Ok, I have received an answer on the mysql mailing list, here it is
> for the record:
>
> From: mysql@subtropolix.org
> Subject: Re: Counting number of associated many-to-many items
> Date: October 26, 2007 10:38:38 PM MDT
> To: mysql@lists.mysql.com
>
>
> 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

Hmmm, in 1. the books table adds nothing to the query.

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
GROUP BY asp.book_id ;

Should do just as well.