List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:May 19 2011 11:37pm
Subject:RE: Complex Query
View as plain text  
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)

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