Heh. The parser is pointing out a simple syntax oversight, yes. The correct syntax for that is select ... from (subselect) aliasname;
----- Original Message ----- > From: "Mimi Cafe" <mimic...@googlemail.com> > To: "Johan De Meersman" <vegiv...@tuxera.be>, "Guido Schlenke" > <galer...@gmx.de> > Cc: mysql@lists.mysql.com > Sent: Friday, 20 May, 2011 11:52:14 PM > 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 > -- 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
