List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:May 20 2011 8:15pm
Subject:Re: Complex Query
View as plain text  
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
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