I meant it may produce unexpected result. Mimi
=> -----Original Message----- => From: Mimi Cafe [mailto:mimic...@googlemail.com] => Sent: 20 May 2011 22:52 => To: 'Johan De Meersman'; 'Guido Schlenke' => Cc: mysql@lists.mysql.com => Subject: RE: Complex Query => => 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
