List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:May 20 2011 9:52pm
Subject:RE: Complex Query
View as plain text  
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:vegivamp@stripped]
=> Sent: 20 May 2011 21:16
=> To: Guido Schlenke
=> Cc: mysql@stripped
=> 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" <galerius@stripped>
=> > To: mysql@stripped
=> > 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:mimicafe@stripped]
=> > Sent: 20 May 2011 11:33
=> > To: 'Anupam Karmarkar'
=> > Cc: 'Guido Schlenke'; mysql@stripped
=> > 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_akarmarkar@stripped]
=> > Sent: 20 May 2011 07:48
=> > To: 'Guido Schlenke'; mysql@stripped; 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=1


Thread
Complex QueryMimi Cafe19 May
  • Re: Complex QueryClaudio Nanni19 May
  • Re: Complex Queryhsv19 May
RE: Complex QueryMimi Cafe19 May
  • Re: Complex QueryGuido Schlenke19 May
  • Re: Complex QueryGuido Schlenke19 May
    • RE: Complex QueryMimi Cafe20 May
      • RE: Complex QueryAnupam Karmarkar20 May
        • RE: Complex QueryMimi Cafe20 May
          • RE: Complex QueryMimi Cafe20 May
            • Re: Complex QueryGuido Schlenke20 May
              • Re: Complex QueryJohan De Meersman20 May
                • RE: Complex QueryMimi Cafe20 May
                  • RE: Complex QueryMimi Cafe20 May
                  • Re: Complex QueryJohan De Meersman21 May
                    • Re: Complex Queryhsv21 May
                      • RE: Complex QueryMimi Cafe21 May