Hmm. Simply replacing the field list with count(*) should work, too. If you only need the count after having executed the select, I'm pretty sure there's something in the API that gives you that without a second query, although I'll be buggered if I can remember right now.
----- Original Message ----- > From: "Guido Schlenke" <galer...@gmx.de> > To: mysql@lists.mysql.com > Sent: Friday, 20 May, 2011 9:04:32 PM > Subject: Re: Complex Query > > Hi Mimi, > > try this > > select count(*) from > ( 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 ); > > Guido > > "Mimi Cafe" schrieb im Newsbeitrag > news:004701cc16f1$a2d550c0$e87ff240$@com... > > 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. > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
