Hi Guido
I like your powerful query below, but it doesn't answer my question. I guess some
clarification is needed here.
OK, I have books in the database with more than one author. When I query for the title of
books and name of author, I get more rows for books that have more than one author. I
need one row per book even if there is more than one author (concatenate the names of all
the authors as authors).
From the tables below, it can be seen that the book titled "Technology Ventures: From Idea
to Enterprise" is written by three authors. I want to get the record for the book,
including the names of all three authors in one row.
For my purchase, I got as far retrieving all the information I need (except the names of
authors) using the following query:
mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, concat(fname,'
> ',initial,' ',lname) as author, pkisbn, publisher FROM book INNER JOIN publisher on
> pkpublisher_id = fkpublisher_id INNER JOIN module_book on pkisbn = fkbook_isbn INNER
> JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER
> JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = 'MSING0010';
+--------------+------------------------------------------+----------+---------------+---------------+------------------------+
| image | title | subtitle | author |
pkisbn | publisher |
+--------------+------------------------------------------+----------+---------------+---------------+------------------------+
| no_image.jpg | Financial Intelligence for Entrepreneurs | | Karen Berman |
9781422119150 | Harvard Business Press |
| no_image.jpg | Financial Intelligence for Entrepreneurs | | Joe Knight |
9781422119150 | Harvard Business Press |
+--------------+------------------------------------------+----------+---------------+---------------+------------------------+
In the above example, I get 2 correct rows, but I want to have one row for this, with the
names of both authors concatenated separated by comma.
Using GROUP_CONCAT, I am able to do just that, but it looks as if my query isn't optimise
or it's wrong as I don't get all the expected rows.
# Looks like this works, but as you can see below, it doesn't.
mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
> group_concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn, publisher FROM book
> INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on
> pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author
> on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr =
> 'MSING0010';
+--------------+------------------------------------------+----------+---------------------------+---------------+------------------------+
| image | title | subtitle | author
| pkisbn | publisher |
+--------------+------------------------------------------+----------+---------------------------+---------------+------------------------+
| no_image.jpg | Financial Intelligence for Entrepreneurs | | Karen Berman,Joe
Knight | 9781422119150 | Harvard Business Press |
+--------------+------------------------------------------+----------+---------------------------+---------------+------------------------+
1 row in set (0.00 sec)
With the WHERE clause above, I get the correct record, but below, I remove the condition
(hoping to get all the books), but instead, I get one row only. Why is this?
# Now is doesn't work.
mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
> group_concat(concat(fname,' ',initial,' ',lname)) as authors, pkisbn, publisher FROM book
> INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on
> pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author
> on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id;
> +--------------+--------------------------------------------+----------+----------------------------------------------------------------------+---------------+-------------------------------+
| image | title | subtitle | authors
| pkisbn | publisher
|
+--------------+--------------------------------------------+----------+----------------------------------------------------------------------+---------------+-------------------------------+
| no_image.jpg | The Origin and Evolution of New Businesses | | Amar V.
Bhide,Eddie McLaney,Peter Atrill,Karen Berman,Joe Knight | 9780195170313 | Oxford
University Press - USA |
+--------------+--------------------------------------------+----------+----------------------------------------------------------------------+---------------+-------------------------------+
1 row in set (0.00 sec)
# this doesn't work at all.
SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, (select
group_concat(concat(fname,' ',initial,' ',lname)) from author inner join book_author on
pkauthor_id = fkauthor_id) as authors, pkisbn, publisher FROM book INNER JOIN publisher
on pkpublisher_id = fkpublisher_id INNER JOIN module_book on pkisbn = fkbook_isbn
INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn
INNER JOIN author on fkauthor_id = pkauthor_id;
Tables from the database.
2 rows in set (0.00 sec) mysql> select * from author;
+-------------+-----------+---------+----------+
| pkauthor_id | fname | initial | lname |
+-------------+-----------+---------+----------+
| 1 | Karen | | Berman |
| 2 | Joe | | Knight |
| 3 | Eddie | | McLaney |
| 4 | Peter | | Atrill |
| 5 | Thomas | H. | Byers |
| 6 | Richard | C. | Dorf |
| 7 | Andrew | J. | Nelson |
| 9 | Christian | S | Albright |
| 10 | Wayne | L. | Winston |
| 11 | Amar | V. | Bhide |
+-------------+-----------+---------+----------+
10 rows in set (0.00 sec)
mysql> select pkisbn, fkpublisher_id, title, subtitle from book;
+---------------+----------------+--------------------------------------------+-------------------------+
| pkisbn | fkpublisher_id | title | subtitle
|
+---------------+----------------+--------------------------------------------+-------------------------+
| 9780071289214 | 1 | Technology Ventures | From Idea
to Enterprise |
| 9780131365483 | 8 | Computer Networking | Top-Down
Approach |
| 9780195170313 | 10 | The Origin and Evolution of New Businesses |
|
| 9780273733652 | 5 | Accounting | An
Introduction |
| 9780324663464 | 12 | Management Science Modeling, Revised |
International Edition |
| 9781422119150 | 3 | Financial Intelligence for Entrepreneurs |
|
+---------------+----------------+--------------------------------------------+-------------------------+
6 rows in set (0.00 sec)
mysql> select pkpublisher_id, publisher from publisher;
+----------------+----------------------------------+
| pkpublisher_id | publisher |
+----------------+----------------------------------+
| 1 | McGraw-Hill |
| 3 | Harvard Business Press |
| 4 | Harper Business New York |
| 5 | FT Prentice Hall |
| 6 | Pitman London |
| 7 | Sams |
| 8 | Pearson |
| 9 | Penguin |
| 10 | Oxford University Press - USA |
| 11 | Oxford University Press - UK |
| 12 | Cengage Learning - South-Western |
+----------------+----------------------------------+
11 rows in set (0.00 sec)
mysql> select * from book_author;
+-------------+---------------+
| fkauthor_id | fkisbn |
+-------------+---------------+
| 1 | 9781422119150 |
| 2 | 9781422119150 |
| 3 | 9780273733652 |
| 4 | 9780273733652 |
| 5 | 9780071289214 |
| 6 | 9780071289214 |
| 7 | 9780071289214 |
| 9 | 9780324663464 |
| 10 | 9780324663464 |
| 11 | 9780195170313 |
+-------------+---------------+
10 rows in set (0.00 sec)
mysql> desc module;
+------------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------------+------+-----+---------+----------------+
| pkmodule_id | mediumint(10) unsigned | NO | PRI | NULL | auto_increment |
| module_nr | varchar(15) | NO | UNI | NULL | |
| fkinstitution_id | mediumint(10) unsigned | NO | MUL | NULL | |
| module_name | varchar(50) | NO | | NULL | |
+------------------+------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc module_book;
+----------------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+----------------+----------------------------------------------------+------+-----+---------+-------+
| fkmodule_nr | varchar(15) | NO | PRI | NULL
| |
| fkbook_isbn | varchar(20) | NO | PRI | NULL
| |
| book_relavance | enum('Required','Core','Recommended','Background') | YES | | NULL
| |
+----------------+----------------------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
