Select count(*) from (select........) did not work. It says: "ERROR 1248 (42000): Every derived table must have its own alias" and I am not sure it really mean aliases.
Although "select found_rows();" works, I am trying to avoid it as the documentation says it may produce expected result - especially after running a query with MySQL limit clause. Mimi => -----Original Message----- => From: Johan De Meersman [mailto:vegiv...@tuxera.be] => Sent: 20 May 2011 21:16 => To: Guido Schlenke => Cc: mysql@lists.mysql.com => Subject: Re: Complex Query => => 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=mimic...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
