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:[email protected]]
Sent: 20 May 2011 11:33
To: 'Anupam Karmarkar'
Cc: 'Guido Schlenke'; [email protected]
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:[email protected]]
Sent: 20 May 2011 07:48
To: 'Guido Schlenke'; [email protected]; 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 <[email protected]> wrote:
From: Mimi Cafe <[email protected]>
Subject: RE: Complex Query
To: "'Guido Schlenke'" <[email protected]>, [email protected]
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/[email protected]
