List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:May 20 2011 10:32am
Subject:RE: Complex Query
View as plain text  
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_akarmarkar@stripped] 
Sent: 20 May 2011 07:48
To: 'Guido Schlenke'; mysql@stripped; 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 <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