Hi Guido,
You need to add group by in your query get desire result
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'
group by IFNULL(image,'no_image.jpg') as image,title, subtitle, pkisbn,
publisher
It should give desire result. As group concat will return group by 1 if you dont specify
group by, which will return only one row, to get result you need to specify group by as
given in above query.
--Anupam
--- On Fri, 20/5/11, Mimi Cafe <mimicafe@stripped> wrote:
From: Mimi Cafe <mimicafe@stripped>
Subject: RE: Complex Query
To: "'Guido Schlenke'" <gschlenke@stripped>, mysql@stripped
Date: Friday, 20 May, 2011, 5:07 AM
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/mysql?unsub=1
