CONCAT statement

[fgarcia44]

I got stuck with this statement.

I have two tables:

Author: author_id, author_name
Wrote: book_id, author id

I want to get book_id with all its authors in one field, concatenating all the authors' names.

For instance, let's suppose we have

Wrote:
book_id author_id
10 50
10 51
10 52
11 53

Author:
author_id author_name
50 John
51 Mike
52 Lisa
53 Rachel

I want to make a select statement which brings:

book_id Authors
10 John, Mike, Lisa
11 Rachel


Please HELP!!!

[james438]

SELECT wrote.book_id, GROUP_CONCAT(author.author_name)
FROM author, wrote
WHERE author.author_id=wrote.author_id
GROUP BY wrote.book_id
This generates:

book_id GROUP_CONCAT(author.author_name)

10 John,Mike,Lisa
11 Rachel

This helps to consolidate the values like you were asking instead of the following where each row with a different name is listed one at a time each with its own row.


SELECT wrote.book_id, author.author_name
FROM author, wrote
WHERE author.author_id=wrote.author_id
which produces:

book_id author_name

10 John
10 Mike
10 Lisa
11 Rachel
I know this is a late response.

Further reading:

GROUP BY (http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html)
GROUP_CONCAT() (http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat)