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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
