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.
--Anupam
--- On Fri, 20/5/11, Mimi Cafe <mimic...@googlemail.com> wrote:
From: Mimi Cafe <mimic...@googlemail.com>
Subject: RE: Complex Query
To: "'Guido Schlenke'" <gschle...@hotmail.de>, mysql@lists.mysql.com
Date: Friday, 20 May, 2011, 5:07 AM
Hi Guido
I like your powerful query below, but it doesn't answer my question. I guess
some clarification is needed here.
OK, I have books in the database with more than one author. When I query for
the title of books and name of author, I get more rows for books that have
more than one author. I need one row per book even if there is more than one
author (concatenate the names of all the authors as authors).
From the tables below, it can be seen that the book titled "Technology
Ventures: From Idea to Enterprise" is written by three authors. I want to
get the record for the book, including the names of all three authors in one
row.
For my purchase, I got as far retrieving all the information I need (except
the names of authors) using the following query:
mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
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';
+--------------+------------------------------------------+----------+------
---------+---------------+------------------------+
| image | title | subtitle |
author | pkisbn | publisher |
+--------------+------------------------------------------+----------+------
---------+---------------+------------------------+
| no_image.jpg | Financial Intelligence for Entrepreneurs | | Karen
Berman | 9781422119150 | Harvard Business Press |
| no_image.jpg | Financial Intelligence for Entrepreneurs | | Joe
Knight | 9781422119150 | Harvard Business Press |
+--------------+------------------------------------------+----------+------
---------+---------------+------------------------+
In the above example, I get 2 correct rows, but I want to have one row for
this, with the names of both authors concatenated separated by comma.
Using GROUP_CONCAT, I am able to do just that, but it looks as if my query
isn't optimise or it's wrong as I don't get all the expected rows.
# Looks like this works, but as you can see below, it doesn't.
mysql> 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';
+--------------+------------------------------------------+----------+------
---------------------+---------------+------------------------+
| image | title | subtitle |
author | pkisbn | publisher |
+--------------+------------------------------------------+----------+------
---------------------+---------------+------------------------+
| no_image.jpg | Financial Intelligence for Entrepreneurs | | Karen
Berman,Joe Knight | 9781422119150 | Harvard Business Press |
+--------------+------------------------------------------+----------+------
---------------------+---------------+------------------------+
1 row in set (0.00 sec)
With the WHERE clause above, I get the correct record, but below, I remove
the condition (hoping to get all the books), but instead, I get one row
only. Why is this?
# Now is doesn't work.
mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
group_concat(concat(fname,' ',initial,' ',lname)) as authors, 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;
+--------------+--------------------------------------------+----------+----
------------------------------------------------------------------+---------
------+-------------------------------+
| image | title | subtitle |
authors |
pkisbn | publisher |
+--------------+--------------------------------------------+----------+----
------------------------------------------------------------------+---------
------+-------------------------------+
| no_image.jpg | The Origin and Evolution of New Businesses | |
Amar V. Bhide,Eddie McLaney,Peter Atrill,Karen Berman,Joe Knight |
9780195170313 | Oxford University Press - USA |
+--------------+--------------------------------------------+----------+----
------------------------------------------------------------------+---------
------+-------------------------------+
1 row in set (0.00 sec)
# this doesn't work at all.
SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, (select
group_concat(concat(fname,' ',initial,' ',lname)) from author inner join
book_author on pkauthor_id = fkauthor_id) as authors, 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;
Tables from the database.
2 rows in set (0.00 sec) mysql> select * from author;
+-------------+-----------+---------+----------+
| pkauthor_id | fname | initial | lname |
+-------------+-----------+---------+----------+
| 1 | Karen | | Berman |
| 2 | Joe | | Knight |
| 3 | Eddie | | McLaney |
| 4 | Peter | | Atrill |
| 5 | Thomas | H. | Byers |
| 6 | Richard | C. | Dorf |
| 7 | Andrew | J. | Nelson |
| 9 | Christian | S | Albright |
| 10 | Wayne | L. | Winston |
| 11 | Amar | V. | Bhide |
+-------------+-----------+---------+----------+
10 rows in set (0.00 sec)
mysql> select pkisbn, fkpublisher_id, title, subtitle from book;
+---------------+----------------+------------------------------------------
--+-------------------------+
| pkisbn | fkpublisher_id | title
| subtitle |
+---------------+----------------+------------------------------------------
--+-------------------------+
| 9780071289214 | 1 | Technology Ventures
| From Idea to Enterprise |
| 9780131365483 | 8 | Computer Networking
| Top-Down Approach |
| 9780195170313 | 10 | The Origin and Evolution of New
Businesses | |
| 9780273733652 | 5 | Accounting
| An Introduction |
| 9780324663464 | 12 | Management Science Modeling, Revised
| International Edition |
| 9781422119150 | 3 | Financial Intelligence for Entrepreneurs
| |
+---------------+----------------+------------------------------------------
--+-------------------------+
6 rows in set (0.00 sec)
mysql> select pkpublisher_id, publisher from publisher;
+----------------+----------------------------------+
| pkpublisher_id | publisher |
+----------------+----------------------------------+
| 1 | McGraw-Hill |
| 3 | Harvard Business Press |
| 4 | Harper Business New York |
| 5 | FT Prentice Hall |
| 6 | Pitman London |
| 7 | Sams |
| 8 | Pearson |
| 9 | Penguin |
| 10 | Oxford University Press - USA |
| 11 | Oxford University Press - UK |
| 12 | Cengage Learning - South-Western |
+----------------+----------------------------------+
11 rows in set (0.00 sec)
mysql> select * from book_author;
+-------------+---------------+
| fkauthor_id | fkisbn |
+-------------+---------------+
| 1 | 9781422119150 |
| 2 | 9781422119150 |
| 3 | 9780273733652 |
| 4 | 9780273733652 |
| 5 | 9780071289214 |
| 6 | 9780071289214 |
| 7 | 9780071289214 |
| 9 | 9780324663464 |
| 10 | 9780324663464 |
| 11 | 9780195170313 |
+-------------+---------------+
10 rows in set (0.00 sec)
mysql> desc module;
+------------------+------------------------+------+-----+---------+--------
--------+
| Field | Type | Null | Key | Default | Extra
|
+------------------+------------------------+------+-----+---------+--------
--------+
| pkmodule_id | mediumint(10) unsigned | NO | PRI | NULL |
auto_increment |
| module_nr | varchar(15) | NO | UNI | NULL |
|
| fkinstitution_id | mediumint(10) unsigned | NO | MUL | NULL |
|
| module_name | varchar(50) | NO | | NULL |
|
+------------------+------------------------+------+-----+---------+--------
--------+
4 rows in set (0.00 sec)
mysql> desc module_book;
+----------------+----------------------------------------------------+-----
-+-----+---------+-------+
| Field | Type | Null
| Key | Default | Extra |
+----------------+----------------------------------------------------+-----
-+-----+---------+-------+
| fkmodule_nr | varchar(15) | NO
| PRI | NULL | |
| fkbook_isbn | varchar(20) | NO
| PRI | NULL | |
| book_relavance | enum('Required','Core','Recommended','Background') | YES
| | NULL | |
+----------------+----------------------------------------------------+-----
-+-----+---------+-------+
3 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
