RE: Complex Query

Anupam Karmarkar Thu, 19 May 2011 23:48:09 -0700

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

RE: Complex Query

Reply via email to