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
