Hi
I now need to determine the number of rows returned by this query below. Wrapping it within SELECT COUNT did not work as expected. SELECT module_nr, IFNULL(image,'no_image.jpg'),title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)), pkisbn, publisher,publication_year, edition, cover_type, pages 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 = ? group by IFNULL(image,'no_image.jpg'),title, subtitle, pkisbn, publisher; Mimi From: Mimi Cafe [mailto:mimic...@googlemail.com] Sent: 20 May 2011 11:33 To: 'Anupam Karmarkar' Cc: 'Guido Schlenke'; mysql@lists.mysql.com Subject: RE: Complex Query Hi guys That's cool! It looks like my query was good except that I miss the "group by". Now I only had to remove the "as image" from the grouping below and it works fine. Thanks guys Mimi From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: 20 May 2011 07:48 To: 'Guido Schlenke'; mysql@lists.mysql.com; Mimi Cafe Subject: RE: Complex Query 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 <mimic...@googlemail.com> wrote: From: Mimi Cafe <mimic...@googlemail.com> Subject: RE: Complex Query To: "'Guido Schlenke'" <gschle...@hotmail.de>, mysql@lists.mysql.com 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=sb_akarmar...@yahoo.com