Counting number of associated many-to-many items

5 Message(s) by 3 Author(s) originally posted in mysql discussion

From: herbasher

Date: Friday, October 26, 2007

Hello!

I have three table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

s, mapping

1. <mathematics> (Or "map", "mapping") If D and C are sets
	(the domain and codomain) then a function f from D to C,
	normally written "f : D -> C" is a subset of D x C such that:

	1. For each d in D there exists some c in C such that (d,c) is
	an element of f.  I.e. the function is defined for every
	element of D.

	2. For each d in D, c1 and c2 in C, if both (d,c1) and (d,c2)
	are elements of f then c1 = c2.  I.e. the function is uniquely
	defined for every element of D.

	See also {image}, {inverse}, {partial function}.

	2. <programming> Computing usage derives from the mathematical
	term but is much less strict.  In programming (except in
	{functional programming}), a function may return different
	values each time it is called with the same argument values
	and may have {side effects}.

	A {procedure} is a function which returns no value but has
	only {side-effects}.  The {C} language, for example, has no
	procedures, only functions.  {ANSI C} even defines a {type},
	{void}, for the result of a function that has no result.

	(1996-09-01)

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

From: herbasher

Date: Friday, October 26, 2007

wrote in message:

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

From: Kees Nuyt

Date: Friday, October 26, 2007

On Fri, 26 Oct 2007 14:29:26 -0700, herbasher
wrote in message:

wrote in message:
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 will get some response later.
--
( Kees
)
c[_] The desire to become a politician should bar

1. <programming, convention> /bar/ The second {metasyntactic
	variable}, after {foo} and before {baz}.  E.g. "Suppose
	function FOO calls functions BAR..."

	2. Often appended to {foo} to produce {foobar}.

	[{Jargon File}]

	(1995-03-07)

you
for life from ever becoming one. (Billy Connolly) (#232)

From: herbasher

Date: Saturday, October 27, 2007

Ok, I have received an answer on the mysql mailing list

<data> A data structure holding many values, possibly of
	different types, which is usually accessed sequentially,
	working from the head to the end of the tail - an "ordered
	list".  This contrasts with a (one-dimensional) {array}, any
	element of which can be accessed equally quickly.

	Lists are often stored using a cell and pointer arrangement
	where each value is stored in a cell along with an associated
	pointer to the next cell.  A special pointer, e.g. zero, marks
	the end of the list.  This is known as a (singly) "linked
	list".  A doubly linked list has pointers from each cell to
	both next and previous cells.

	An unordered list is a {set}.

	(1998-11-12)

, here it is
for the record:

From: mysql@xxxxxxxxxxx
Subject: Re: Counting number of associated many-to-many items
Date: October 26, 2007 10:38:38 PM MDT
To: mysql@xxxxxxxxxxx!
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

1. <logic> A {logical formula} in {conjunctive normal form},
	which has the {schema}

	 p1 ^ ...^ pm => q1 V ... V qn.

	or, equivalently,

	 ~p1 V ... V ~pn V q1 V ... V qn,

	where pi and qi are {atoms}.
	
	The operators ~, ^, V, => are {connectives}, where ~ stands
	for {negation}, ^ for {conjunction}, V for {disjunction} and
	=> for {implication}.

	2. <database> A part of a {SQL} statement that does not
	constitute a full statement; for e.g. a "WHERE clause".

	(2004-05-28)

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@xxxxxxxxxxx

From: Paul Lautman

Date: Saturday, October 27, 2007

wrote in message:

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

Next Message: Copy data from MsSql database to a MySql database in batch mode.

Blogs related to Counting number of associated many-to-many items

ScissosShear_UltraShortBobBlone
In 1969, following a failed attempt by Lennon and McCartney to buy the company, James and Silver sold Northern Songs to British TV company Associated TeleVision (ATV), from which Lennon and McCartney received stock. ...

24 new messages in 13 topics - digest
to make use of my has_many association. While it does bring a little > Request knowledge into User, I think user.requests.find_active is > cleaner and makes more sense than Request.find_active(user). To me, ...

25 new messages in 19 topics - digest
Error: Unknown database 'shovell_development' plus a number of other 'from' lines. I created the shovell_development database earlier exactly as the book instructed & I've used the show command in mysql plus manually checking ...