List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:May 20 2011 9:56pm
Subject:RE: Complex Query
View as plain text  
I meant it may produce unexpected result.

Mimi

=> -----Original Message-----
=> From: Mimi Cafe [mailto:mimicafe@stripped]
=> Sent: 20 May 2011 22:52
=> To: 'Johan De Meersman'; 'Guido Schlenke'
=> Cc: mysql@stripped
=> 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: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