List:General Discussion« Previous MessageNext Message »
From:Anupam Karmarkar Date:May 20 2011 6:47am
Subject:RE: Complex Query
View as plain text  
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 <mimicafe@stripped> wrote:

From: Mimi Cafe <mimicafe@stripped>
Subject: RE: Complex Query
To: "'Guido Schlenke'" <gschlenke@stripped>, mysql@stripped
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=1


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