SEO

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 help me out with this?



Thank you,
Rob

On Oct 26, 11:06 am, herbasher <rob...@gmail.com> 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.
> 2. Select each author, and how many bestseller books (bestseller = 1)
> he has worked on.
>
> Not exactly sure how to do this, can someone help me out with this?

Is this hard? Possible? Expensive?


Thanks

On Fri, 26 Oct 2007 14:29:26 -0700, herbasher
<robmnl@gmail.com> wrote:

>On Oct 26, 11:06 am, herbasher <rob...@gmail.com> 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.
>> 2. Select each author, and how many bestseller books (bestseller = 1)
>> he has worked on.
>>
>> Not exactly sure how to do this, can someone help me out with this?
>
>Is this hard? Possible? Expensive?

No, but it's Friday
I'm sure you'll get some response later.
--
( Kees
)
c[_] The desire to become a politician should bar you
for life from ever becoming one. (Billy Connolly) (#232)

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=robmnl@gmail.com

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.