From: Anupam Karmarkar Date: May 20 2011 6:47am Subject: RE: Complex Query List-Archive: http://lists.mysql.com/mysql/225051 Message-Id: <529072.95043.qm@web94716.mail.in2.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1388225020-1305874059=:95043" --0-1388225020-1305874059=:95043 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi Guido, You need to add group by in your query get desire result SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, =0Agroup_conc= at(concat(fname,' ',initial,' ',lname)) as author, pkisbn, =0Apublisher FRO= M book INNER JOIN publisher on pkpublisher_id =3D =0Afkpublisher_id INNER J= OIN module_book on=A0 =A0=A0=A0pkisbn =3D fkbook_isbn INNER =0AJOIN module = on fkmodule_nr =3D module_nr INNER JOIN book_author on pkisbn =3D=0A fkisbn= INNER JOIN author on fkauthor_id =3D pkauthor_id=20 WHERE module_nr =3D 'MSING0010' group by IFNULL(image,'no_image.jpg') as image,title, subtitle, pkisbn, = =0Apublisher 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 n= eed to specify group by as given in above query. --Anupam --- On Fri, 20/5/11, Mimi Cafe wrote: From: Mimi Cafe Subject: RE: Complex Query To: "'Guido Schlenke'" , 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 gues= s some clarification is needed here. OK, I have books in the database with more than one author. When I query fo= r the title of books and name of author, I get more rows for books that hav= e 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 Vent= ures: From Idea to Enterprise" is written by three authors. I want to get t= he 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 =3D fkpublisher_id INNER JOIN module_book = on=A0 =A0=A0=A0pkisbn =3D fkbook_isbn INNER JOIN module on fkmodule_nr =3D = module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER JOIN author on = fkauthor_id =3D pkauthor_id=A0 =A0=A0=A0WHERE module_nr =3D 'MSING0010'; +--------------+------------------------------------------+----------+-----= ----------+---------------+------------------------+ | image=A0 =A0 =A0 =A0 | title=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle | author=A0 =A0 =A0 =A0 | pkisbn=A0 = =A0 =A0 =A0 | publisher=A0 =A0 =A0 =A0 =A0 =A0 =A0 | +--------------+------------------------------------------+----------+-----= ----------+---------------+------------------------+ | no_image.jpg | Financial Intelligence for Entrepreneurs |=A0 =A0 =A0 =A0 = =A0 | Karen=A0 Berman | 9781422119150 | Harvard Business Press | | no_image.jpg | Financial Intelligence for Entrepreneurs |=A0 =A0 =A0 =A0 = =A0 | Joe=A0 Knight=A0=A0=A0| 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.=20 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. =20 mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_= concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn, publisher FR= OM book INNER JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JOI= N module_book on=A0 =A0=A0=A0pkisbn =3D fkbook_isbn INNER JOIN module on fk= module_nr =3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER J= OIN author on fkauthor_id =3D pkauthor_id=A0 WHERE module_nr =3D 'MSING0010= '; +--------------+------------------------------------------+----------+-----= ----------------------+---------------+------------------------+ | image=A0 =A0 =A0 =A0 | title=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle | author=A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 | pkisbn=A0 =A0 =A0 =A0 | publisher=A0 =A0 =A0 =A0 =A0 =A0 =A0 = | +--------------+------------------------------------------+----------+-----= ----------------------+---------------+------------------------+ | no_image.jpg | Financial Intelligence for Entrepreneurs |=A0 =A0 =A0 =A0 = =A0 | Karen=A0 Berman,Joe=A0 Knight | 9781422119150 | Harvard Business Pres= s | +--------------+------------------------------------------+----------+-----= ----------------------+---------------+------------------------+ 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 onl= y. Why is this? # Now is doesn't work.=20 mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_= concat(concat(fname,' ',initial,' ',lname)) as authors, pkisbn, publisher F= ROM book INNER JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JO= IN module_book on=A0 =A0=A0=A0pkisbn =3D fkbook_isbn INNER JOIN module on f= kmodule_nr =3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER = JOIN author on fkauthor_id =3D pkauthor_id;=A0 =A0 =A0 =A0 =A0 =A0 =A0 +---= -----------+--------------------------------------------+----------+-------= ---------------------------------------------------------------+-----------= ----+-------------------------------+ | image=A0 =A0 =A0 =A0 | title=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle | authors=A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 | pkisbn=A0 =A0 =A0 =A0 | publisher=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A0| +--------------+--------------------------------------------+----------+---= -------------------------------------------------------------------+-------= --------+-------------------------------+ | no_image.jpg | The Origin and Evolution of New Businesses |=A0 =A0 =A0 = =A0 =A0 | Amar V. Bhide,Eddie=A0 McLaney,Peter=A0 Atrill,Karen=A0 Berman,Jo= e=A0 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_au= thor on pkauthor_id =3D fkauthor_id) as authors, pkisbn, publisher FROM boo= k INNER JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JOIN modu= le_book on=A0 =A0 pkisbn =3D fkbook_isbn INNER JOIN module on fkmodule_nr = =3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER JOIN author= on fkauthor_id =3D pkauthor_id; Tables from the database. 2 rows in set (0.00 sec) mysql> select * from author; +-------------+-----------+---------+----------+ | pkauthor_id | fname=A0 =A0=A0=A0| initial | lname=A0 =A0 | +-------------+-----------+---------+----------+ |=A0 =A0 =A0 =A0 =A0=A0=A01 | Karen=A0 =A0=A0=A0|=A0 =A0 =A0 =A0=A0=A0| Ber= man=A0=A0=A0| |=A0 =A0 =A0 =A0 =A0=A0=A02 | Joe=A0 =A0 =A0=A0=A0|=A0 =A0 =A0 =A0=A0=A0| K= night=A0=A0=A0| |=A0 =A0 =A0 =A0 =A0=A0=A03 | Eddie=A0 =A0=A0=A0|=A0 =A0 =A0 =A0=A0=A0| McL= aney=A0 | |=A0 =A0 =A0 =A0 =A0=A0=A04 | Peter=A0 =A0=A0=A0|=A0 =A0 =A0 =A0=A0=A0| Atr= ill=A0=A0=A0| |=A0 =A0 =A0 =A0 =A0=A0=A05 | Thomas=A0 =A0 | H.=A0 =A0 =A0 | Byers=A0 =A0 = | |=A0 =A0 =A0 =A0 =A0=A0=A06 | Richard=A0=A0=A0| C.=A0 =A0 =A0 | Dorf=A0 =A0= =A0=A0| |=A0 =A0 =A0 =A0 =A0=A0=A07 | Andrew=A0 =A0 | J.=A0 =A0 =A0 | Nelson=A0=A0= =A0| |=A0 =A0 =A0 =A0 =A0=A0=A09 | Christian | S=A0 =A0 =A0=A0=A0| Albright | |=A0 =A0 =A0 =A0 =A0 10 | Wayne=A0 =A0=A0=A0| L.=A0 =A0 =A0 | Winston=A0 | |=A0 =A0 =A0 =A0 =A0 11 | Amar=A0 =A0 =A0 | V.=A0 =A0 =A0 | Bhide=A0 =A0 | +-------------+-----------+---------+----------+ 10 rows in set (0.00 sec) mysql> select pkisbn, fkpublisher_id, title, subtitle from book; +---------------+----------------+-----------------------------------------= ---+-------------------------+ | pkisbn=A0 =A0 =A0 =A0 | fkpublisher_id | title=A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 | +---------------+----------------+-----------------------------------------= ---+-------------------------+ | 9780071289214 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 1 | Technology Ventures=A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | From Idea to Enterprise | | 9780131365483 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 8 | Computer Networking=A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | Top-Down Approach=A0 =A0 =A0=A0= =A0| | 9780195170313 |=A0 =A0 =A0 =A0 =A0 =A0=A0=A010 | The Origin and Evolution= of New Businesses |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A0| | 9780273733652 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 5 | Accounting=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A0| An Introduction=A0 = =A0 =A0 =A0=A0=A0| | 9780324663464 |=A0 =A0 =A0 =A0 =A0 =A0=A0=A012 | Management Science Model= ing, Revised=A0 =A0 =A0=A0=A0| International Edition=A0=A0=A0| | 9781422119150 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 3 | Financial Intelligence for= Entrepreneurs=A0=A0=A0|=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0= =A0| +---------------+----------------+-----------------------------------------= ---+-------------------------+ 6 rows in set (0.00 sec) mysql> select pkpublisher_id, publisher from publisher; +----------------+----------------------------------+ | pkpublisher_id | publisher=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= | +----------------+----------------------------------+ |=A0 =A0 =A0 =A0 =A0 =A0 =A0 1 | McGraw-Hill=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 | |=A0 =A0 =A0 =A0 =A0 =A0 =A0 3 | Harvard Business Press=A0 =A0 =A0 =A0 =A0= =A0=A0| |=A0 =A0 =A0 =A0 =A0 =A0 =A0 4 | Harper Business New York=A0 =A0 =A0 =A0=A0= =A0| |=A0 =A0 =A0 =A0 =A0 =A0 =A0 5 | FT Prentice Hall=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0=A0=A0| |=A0 =A0 =A0 =A0 =A0 =A0 =A0 6 | Pitman London=A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 | |=A0 =A0 =A0 =A0 =A0 =A0 =A0 7 | Sams=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0=A0=A0| |=A0 =A0 =A0 =A0 =A0 =A0 =A0 8 | Pearson=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 | |=A0 =A0 =A0 =A0 =A0 =A0 =A0 9 | Penguin=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 | |=A0 =A0 =A0 =A0 =A0 =A0=A0=A010 | Oxford University Press - USA=A0 =A0 | |=A0 =A0 =A0 =A0 =A0 =A0=A0=A011 | Oxford University Press - UK=A0 =A0=A0= =A0| |=A0 =A0 =A0 =A0 =A0 =A0=A0=A012 | Cengage Learning - South-Western | +----------------+----------------------------------+ 11 rows in set (0.00 sec) mysql> select * from book_author; +-------------+---------------+ | fkauthor_id | fkisbn=A0 =A0 =A0 =A0 | +-------------+---------------+ |=A0 =A0 =A0 =A0 =A0=A0=A01 | 9781422119150 | |=A0 =A0 =A0 =A0 =A0=A0=A02 | 9781422119150 | |=A0 =A0 =A0 =A0 =A0=A0=A03 | 9780273733652 | |=A0 =A0 =A0 =A0 =A0=A0=A04 | 9780273733652 | |=A0 =A0 =A0 =A0 =A0=A0=A05 | 9780071289214 | |=A0 =A0 =A0 =A0 =A0=A0=A06 | 9780071289214 | |=A0 =A0 =A0 =A0 =A0=A0=A07 | 9780071289214 | |=A0 =A0 =A0 =A0 =A0=A0=A09 | 9780324663464 | |=A0 =A0 =A0 =A0 =A0 10 | 9780324663464 | |=A0 =A0 =A0 =A0 =A0 11 | 9780195170313 | +-------------+---------------+ 10 rows in set (0.00 sec) mysql> desc module; +------------------+------------------------+------+-----+---------+-------= ---------+ | Field=A0 =A0 =A0 =A0 =A0 =A0 | Type=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0= =A0| Null | Key | Default | Extra=A0 =A0 =A0 =A0 =A0 | +------------------+------------------------+------+-----+---------+-------= ---------+ | pkmodule_id=A0 =A0 =A0 | mediumint(10) unsigned | NO=A0=A0=A0| PRI | NULL= =A0 =A0 | auto_increment | | module_nr=A0 =A0 =A0 =A0 | varchar(15)=A0 =A0 =A0 =A0 =A0 =A0 | NO=A0=A0= =A0| UNI | NULL=A0 =A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | | fkinstitution_id | mediumint(10) unsigned | NO=A0=A0=A0| MUL | NULL=A0 = =A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | | module_name=A0 =A0 =A0 | varchar(50)=A0 =A0 =A0 =A0 =A0 =A0 | NO=A0=A0=A0= |=A0 =A0=A0=A0| NULL=A0 =A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | +------------------+------------------------+------+-----+---------+-------= ---------+ 4 rows in set (0.00 sec) mysql> desc module_book; +----------------+----------------------------------------------------+----= --+-----+---------+-------+ | Field=A0 =A0 =A0 =A0 =A0 | Type=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=A0| Null | Key | Def= ault | Extra | +----------------+----------------------------------------------------+----= --+-----+---------+-------+ | fkmodule_nr=A0 =A0 | varchar(15)=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | NO=A0=A0=A0| PRI | NULL=A0 =A0 |= =A0 =A0 =A0=A0=A0| | fkbook_isbn=A0 =A0 | varchar(20)=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | NO=A0=A0=A0| PRI | NULL=A0 =A0 |= =A0 =A0 =A0=A0=A0| | book_relavance | enum('Required','Core','Recommended','Background') | YES= =A0 |=A0 =A0=A0=A0| NULL=A0 =A0 |=A0 =A0 =A0=A0=A0| +----------------+----------------------------------------------------+----= --+-----+---------+-------+ 3 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dsb_akarmarkar@y= ahoo.com --0-1388225020-1305874059=:95043--