Complex Query

--bcaec51d2600703e4504a3937c6f
Content-Type: text/plain; charset=ISO-8859-1

Hi

I am trying to retrieve record from 2 tables (book and author), but my
problem is how to retrieve all the names of authors where more than one
author wrote a book.

Here is what i have: it works OK, except that it returns more than one row
for books that have more than one author.

select concat(fname,' ',initial,' ',lname) from author inner join
book_author on fkauthor_id = pkauthor_id inner join book on fkisbn = pkisbn;

Any idea who can formulate my query to retrieve the books with with names of
all the authors concatenated in one?


Mimi

--bcaec51d2600703e4504a3937c6f--

Re: Complex Query

>>>> 2011/05/18 22:22 +0100, Mimi Cafe >>>>
I am trying to retrieve record from 2 tables (book and author), but my
problem is how to retrieve all the names of authors where more than one
author wrote a book.

Here is what i have: it works OK, except that it returns more than one row
for books that have more than one author.

select concat(fname,' ',initial,' ',lname) from author inner join
book_author on fkauthor_id = pkauthor_id inner join book on fkisbn = pkisbn;
<<<<<<<<
You are looking for grouping (aggregating) functions. But how do your tables look? and at first you mention two tables, but your query has three.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Query

--20cf3054ab590cbca004a39dd3f0
Content-Type: text/plain; charset=ISO-8859-1

Hint:

- group_concat
- group by
- having count(*)>1

Cheers

Claudio


2011/5/18 Mimi Cafe

> Hi
>
> I am trying to retrieve record from 2 tables (book and author), but my
> problem is how to retrieve all the names of authors where more than one
> author wrote a book.
>
> Here is what i have: it works OK, except that it returns more than one row
> for books that have more than one author.
>
> select concat(fname,' ',initial,' ',lname) from author inner join
> book_author on fkauthor_id = pkauthor_id inner join book on fkisbn =
> pkisbn;
>
> Any idea who can formulate my query to retrieve the books with with names
> of
> all the authors concatenated in one?
>
>
> Mimi
>



--
Claudio

--20cf3054ab590cbca004a39dd3f0--

RE: Complex Query

The tables have following structure. Both keys in table book_author are not
primary keys (should they?).

Author book_author book
publisher subject subject_book
====== ============ ====== ==========
======== =============
fkauthor_id fkisbn pkisbn
pkpublisher_id pksubject_id fksubject_id
firstname fkauthor_id
fkpublisher_id subject fkisbn
middlename
lastname

Now, for reason unknown to me, the reference between the first 3 tables
don't seem to exist although I have created them.
My query works for the most of it, but selecting all the authors for a book
in one query seems complex and I don't know how to go about it except to use
a second query to retrieve the authors names. Also, any ideas why the
relation between the first 3 tables don't work? In mysql, I run the command
similar to the one below to establish the relationships between the tables
(without error), but in MySQL Workbench, I cannot see the relationship
between the first three tables above. When I try to create the relations in
Workbench it failed. It tells me no primary keys columns in book_author.

alter table book_author add foreign key (fkisbn) references book (pkisbn); #
the works in mysql without error, but no relation can be seen when the
module is created in mysql workbench.



Thanks

Mimi



=> -----Original Message-----
=> From: George Pitcher [mailto:George.Pitcher@publishingtechnology.com]
=> Sent: 19 May 2011 06:42
=> To: Mimi Cafe
=> Subject: RE: Complex Query
=>
=> Mimi,
=>
=> I have a lot of biblio records across several applications. I decided
=> at
=> the outset not to break up the author names. As long as you are able
=> to
=> identify that an author is associated with a book, it should be
=> straightforward to extract the names. The difficulty that I see
=> (without
=> knowing your table structure, is how to get the author names to appear
=> in the correct order.
=>
=> If I was setting this up from scratch, I would use a third table and
=> in
=> this I would store the author id, book id and the name position. From
=> the three, it should be possible to construct the concatenated author
=> names.
=>
=> I tend to do the concatenation of such things in PHP rather than sql,
=> but that's just my way.
=>
=> As for the multiple rows, try using distinct().
=>
=> Hope this helps
=>
=> George
=>
=> -----Original Message-----
=> From: Mimi Cafe [mailto:mimicafe@googlemail.com]
=> Sent: 18 May 2011 22:23
=> To: mysql@lists.mysql.com
=> Subject: Complex Query
=>
=> Hi
=>
=> I am trying to retrieve record from 2 tables (book and author), but my
=> problem is how to retrieve all the names of authors where more than
=> one
=> author wrote a book.
=>
=> Here is what i have: it works OK, except that it returns more than one
=> row
=> for books that have more than one author.
=>
=> select concat(fname,' ',initial,' ',lname) from author inner join
=> book_author on fkauthor_id = pkauthor_id inner join book on fkisbn =
=> pkisbn;
=>
=> Any idea who can formulate my query to retrieve the books with with
=> names of
=> all the authors concatenated in one?
=>
=>
=> Mimi
=>
=> The information in this message is intended solely for the addressee
=> and should be considered confidential. Publishing Technology does not
=> accept legal responsibility for the contents of this message and any
=> statements contained herein which do not relate to the official
=> business of Publishing Technology are neither given nor endorsed by
=> Publishing Technology and are those of the individual and not of
=> Publishing Technology. This message has been scanned for viruses using
=> the most current and reliable tools available and Publishing
=> Technology excludes all liability related to any viruses that might
=> exist in any attachment or which may have been acquired in transit.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Query

Hi Mimi,

I'm not quite sure, if I did understand you right, but let's try the
following query:

select a.firstname, a.lastname, b.subject
from Author a, book_author ba, book b
where a.fkauthor_id IN
( select fkauthor_id, count(*) from book_author group by fkauthor_id having
count(*) > 1 )
and a.fkauthor_id = ba.fkauthor_id
and ba.fkisbn = b.pkisbn;

Hope it works.

Best regards
Guido

"Mimi Cafe" schrieb im Newsbeitrag
news:000101cc1611$8ba4a4e0$a2edeea0$@com...

The tables have following structure. Both keys in table book_author are not
primary keys (should they?).

Author book_author book
publisher subject subject_book
====== ============ ====== ==========
======== =============
fkauthor_id fkisbn pkisbn
pkpublisher_id pksubject_id fksubject_id
firstname fkauthor_id
fkpublisher_id subject fkisbn
middlename
lastname

Now, for reason unknown to me, the reference between the first 3 tables
don't seem to exist although I have created them.
My query works for the most of it, but selecting all the authors for a book
in one query seems complex and I don't know how to go about it except to use
a second query to retrieve the authors names. Also, any ideas why the
relation between the first 3 tables don't work? In mysql, I run the command
similar to the one below to establish the relationships between the tables
(without error), but in MySQL Workbench, I cannot see the relationship
between the first three tables above. When I try to create the relations in
Workbench it failed. It tells me no primary keys columns in book_author.

alter table book_author add foreign key (fkisbn) references book (pkisbn); #
the works in mysql without error, but no relation can be seen when the
module is created in mysql workbench.



Thanks

Mimi



=> -----Original Message-----
=> From: George Pitcher [mailto:George.Pitcher@publishingtechnology.com]
=> Sent: 19 May 2011 06:42
=> To: Mimi Cafe
=> Subject: RE: Complex Query
=>
=> Mimi,
=>
=> I have a lot of biblio records across several applications. I decided
=> at
=> the outset not to break up the author names. As long as you are able
=> to
=> identify that an author is associated with a book, it should be
=> straightforward to extract the names. The difficulty that I see
=> (without
=> knowing your table structure, is how to get the author names to appear
=> in the correct order.
=>
=> If I was setting this up from scratch, I would use a third table and
=> in
=> this I would store the author id, book id and the name position. From
=> the three, it should be possible to construct the concatenated author
=> names.
=>
=> I tend to do the concatenation of such things in PHP rather than sql,
=> but that's just my way.
=>
=> As for the multiple rows, try using distinct().
=>
=> Hope this helps
=>
=> George
=>
=> -----Original Message-----
=> From: Mimi Cafe [mailto:mimicafe@googlemail.com]
=> Sent: 18 May 2011 22:23
=> To: mysql@lists.mysql.com
=> Subject: Complex Query
=>
=> Hi
=>
=> I am trying to retrieve record from 2 tables (book and author), but my
=> problem is how to retrieve all the names of authors where more than
=> one
=> author wrote a book.
=>
=> Here is what i have: it works OK, except that it returns more than one
=> row
=> for books that have more than one author.
=>
=> select concat(fname,' ',initial,' ',lname) from author inner join
=> book_author on fkauthor_id = pkauthor_id inner join book on fkisbn =
=> pkisbn;
=>
=> Any idea who can formulate my query to retrieve the books with with
=> names of
=> all the authors concatenated in one?
=>
=>
=> Mimi
=>
=> The information in this message is intended solely for the addressee
=> and should be considered confidential. Publishing Technology does not
=> accept legal responsibility for the contents of this message and any
=> statements contained herein which do not relate to the official
=> business of Publishing Technology are neither given nor endorsed by
=> Publishing Technology and are those of the individual and not of
=> Publishing Technology. This message has been scanned for viruses using
=> the most current and reliable tools available and Publishing
=> Technology excludes all liability related to any viruses that might
=> exist in any attachment or which may have been acquired in transit.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Query

Hi Mimi,

I'm sorry, but my query doesn't work. In between I created 3 tables:

authors:
+-----------+------------------+------+-----+---------+----- -----------
| Field | Type | Null | Key | Default | Extra
+-----------+------------------+------+-----+---------+----- -----------
| a_id | int(10) unsigned | NO | PRI | NULL | auto_increment
| FirstName | varchar(50) | YES | | NULL |
| LastName | varchar(50) | YES | | NULL |

book_authors:
+-------+------------------+------+-----+---------+------
| Field | Type | Null | Key | Default | Extra
+-------+------------------+------+-----+---------+------
| isbn | int(10) unsigned | YES | | NULL |
| au_id | int(10) unsigned | YES | | NULL |

books:
+---------+------------------+------+-----+---------+------
| Field | Type | Null | Key | Default | Extra
+---------+------------------+------+-----+---------+------
| b_isbn | int(10) unsigned | YES | | NULL |
| subject | varchar(50) | YES | | NULL |

Suppose we have the following data:

select * from books;
+--------+-----------------
| b_isbn | subject
+--------+-----------------
| 1 | Der Schneemann
| 2 | Leopard
| 3 | Dschungelbücher
| 4 | Brandmauer

select * from book_authors;
+------+-------+
| isbn | au_id |
+------+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 4 |
| 4 | 3 |

select * from authors;
+------+-----------+----------+
| a_id | FirstName | LastName |
+------+-----------+----------+
| 1 | Jo | Nesbo |
| 2 | Helene | Tursten |
| 3 | Hakan | Nesser |
| 4 | Rudyard | Kipling |

As you can see, there is only one author that has written more than one
title: Jo Nesbo (Schneemann & Leopard).

If you perform the query

SELECT a.a_id, a.LastName, a.FirstName, b.subject
FROM AUTHORS a, book_authors ba, books b
WHERE a.a_id IN
( SELECT au_id FROM
( SELECT au_id, COUNT(*)
FROM `book_authors`
GROUP BY au_id
HAVING COUNT(*) > 1 ) t2 )
AND ba.au_id = a.a_id
AND ba.isbn = b.b_isbn;

you’ll get the answer:
+------+----------+-----------+----------------+
| a_id | LastName | FirstName | subject |
+------+----------+-----------+----------------+
| 1 | Nesbo | Jo | Der Schneemann |
| 1 | Nesbo | Jo | Leopard |
+------+----------+-----------+----------------+
2 rows in set (0.00 sec)

Is this what you want? I supposed that the isbn numbers in tables
‘book_authors’ and ‘books’ are identically.

Best regards,
Guido

"Mimi Cafe" schrieb im Newsbeitrag
news:000101cc1611$8ba4a4e0$a2edeea0$@com...

The tables have following structure. Both keys in table book_author are not
primary keys (should they?).

Author book_author book
publisher subject subject_book
====== ============ ====== ==========
======== =============
fkauthor_id fkisbn pkisbn
pkpublisher_id pksubject_id fksubject_id
firstname fkauthor_id
fkpublisher_id subject fkisbn
middlename
lastname

Now, for reason unknown to me, the reference between the first 3 tables
don't seem to exist although I have created them.
My query works for the most of it, but selecting all the authors for a book
in one query seems complex and I don't know how to go about it except to use
a second query to retrieve the authors names. Also, any ideas why the
relation between the first 3 tables don't work? In mysql, I run the command
similar to the one below to establish the relationships between the tables
(without error), but in MySQL Workbench, I cannot see the relationship
between the first three tables above. When I try to create the relations in
Workbench it failed. It tells me no primary keys columns in book_author.

alter table book_author add foreign key (fkisbn) references book (pkisbn); #
the works in mysql without error, but no relation can be seen when the
module is created in mysql workbench.



Thanks

Mimi



=> -----Original Message-----
=> From: George Pitcher [mailto:George.Pitcher@publishingtechnology.com]
=> Sent: 19 May 2011 06:42
=> To: Mimi Cafe
=> Subject: RE: Complex Query
=>
=> Mimi,
=>
=> I have a lot of biblio records across several applications. I decided
=> at
=> the outset not to break up the author names. As long as you are able
=> to
=> identify that an author is associated with a book, it should be
=> straightforward to extract the names. The difficulty that I see
=> (without
=> knowing your table structure, is how to get the author names to appear
=> in the correct order.
=>
=> If I was setting this up from scratch, I would use a third table and
=> in
=> this I would store the author id, book id and the name position. From
=> the three, it should be possible to construct the concatenated author
=> names.
=>
=> I tend to do the concatenation of such things in PHP rather than sql,
=> but that's just my way.
=>
=> As for the multiple rows, try using distinct().
=>
=> Hope this helps
=>
=> George
=>
=> -----Original Message-----
=> From: Mimi Cafe [mailto:mimicafe@googlemail.com]
=> Sent: 18 May 2011 22:23
=> To: mysql@lists.mysql.com
=> Subject: Complex Query
=>
=> Hi
=>
=> I am trying to retrieve record from 2 tables (book and author), but my
=> problem is how to retrieve all the names of authors where more than
=> one
=> author wrote a book.
=>
=> Here is what i have: it works OK, except that it returns more than one
=> row
=> for books that have more than one author.
=>
=> select concat(fname,' ',initial,' ',lname) from author inner join
=> book_author on fkauthor_id = pkauthor_id inner join book on fkisbn =
=> pkisbn;
=>
=> Any idea who can formulate my query to retrieve the books with with
=> names of
=> all the authors concatenated in one?
=>
=>
=> Mimi
=>
=> The information in this message is intended solely for the addressee
=> and should be considered confidential. Publishing Technology does not
=> accept legal responsibility for the contents of this message and any
=> statements contained herein which do not relate to the official
=> business of Publishing Technology are neither given nor endorsed by
=> Publishing Technology and are those of the individual and not of
=> Publishing Technology. This message has been scanned for viruses using
=> the most current and reliable tools available and Publishing
=> Technology excludes all liability related to any viruses that might
=> exist in any attachment or which may have been acquired in transit.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Complex Query

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 =3D fkpublisher_id INNER =
JOIN module_book on pkisbn =3D fkbook_isbn INNER JOIN module on =
fkmodule_nr =3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn =
INNER JOIN author on fkauthor_id =3D pkauthor_id WHERE module_nr =3D =
'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.=20

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.
=20
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 =3D =
fkpublisher_id INNER JOIN module_book on pkisbn =3D fkbook_isbn =
INNER JOIN module on fkmodule_nr =3D module_nr INNER JOIN book_author on =
pkisbn =3D fkisbn INNER JOIN author on fkauthor_id =3D pkauthor_id =
WHERE module_nr =3D '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.=20

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 =3D =
fkpublisher_id INNER JOIN module_book on pkisbn =3D fkbook_isbn =
INNER JOIN module on fkmodule_nr =3D module_nr INNER JOIN book_author on =
pkisbn =3D fkisbn INNER JOIN author on fkauthor_id =3D 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 =3D fkauthor_id) as authors, pkisbn, =
publisher FROM book INNER JOIN publisher on pkpublisher_id =3D =
fkpublisher_id INNER JOIN module_book on pkisbn =3D fkbook_isbn INNER =
JOIN module on fkmodule_nr =3D module_nr INNER JOIN book_author on =
pkisbn =3D fkisbn INNER JOIN author on fkauthor_id =3D 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Complex Query

--0-1388225020-1305874059=:95043
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Guido,

You need to add group by in your query get desire result

SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, =0Agroup_conc=
at(concat(fname,' ',initial,' ',lname)) as author, pkisbn, =0Apublisher FRO=
M book INNER JOIN publisher on pkpublisher_id =3D =0Afkpublisher_id INNER J=
OIN module_book on=A0   =A0pkisbn =3D fkbook_isbn INNER =0AJOIN module =
on fkmodule_nr =3D module_nr INNER JOIN book_author on pkisbn = fkisbn=
INNER JOIN author on fkauthor_id =3D pkauthor_id=20
WHERE module_nr =3D 'MSING0010'
group by IFNULL(image,'no_image.jpg') as image,title, subtitle, pkisbn, =
=0Apublisher

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 n=
eed to specify group by as given in above query.

--Anupam

--- On Fri, 20/5/11, Mimi Cafe wrote:

From: Mimi Cafe
Subject: RE: Complex Query
To: "'Guido Schlenke'" , 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 gues=
s some clarification is needed here.

OK, I have books in the database with more than one author. When I query fo=
r the title of books and name of author, I get more rows for books that hav=
e 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 Vent=
ures: From Idea to Enterprise" is written by three authors. I want to get t=
he 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 =3D fkpublisher_id INNER JOIN module_book =
on=A0   =A0pkisbn =3D fkbook_isbn INNER JOIN module on fkmodule_nr =3D =
module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER JOIN author on =
fkauthor_id =3D pkauthor_id=A0   =A0WHERE module_nr =3D 'MSING0010';
+--------------+------------------------------------------+- ---------+-----=
----------+---------------+------------------------+
| image=A0 =A0 =A0 =A0 | title=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle | author=A0 =A0 =A0 =A0 | pkisbn=A0 =
=A0 =A0 =A0 | publisher=A0 =A0 =A0 =A0 =A0 =A0 =A0 |
+--------------+------------------------------------------+- ---------+-----=
----------+---------------+------------------------+
| no_image.jpg | Financial Intelligence for Entrepreneurs |=A0 =A0 =A0 =A0 =
=A0 | Karen=A0 Berman | 9781422119150 | Harvard Business Press |
| no_image.jpg | Financial Intelligence for Entrepreneurs |=A0 =A0 =A0 =A0 =
=A0 | Joe=A0 Knight  =A0| 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.=20

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.
=20
mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_=
concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn, publisher FR=
OM book INNER JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JOI=
N module_book on=A0   =A0pkisbn =3D fkbook_isbn INNER JOIN module on fk=
module_nr =3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER J=
OIN author on fkauthor_id =3D pkauthor_id=A0 WHERE module_nr =3D 'MSING0010=
';
+--------------+------------------------------------------+- ---------+-----=
----------------------+---------------+--------------------- ---+
| image=A0 =A0 =A0 =A0 | title=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle | author=A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 | pkisbn=A0 =A0 =A0 =A0 | publisher=A0 =A0 =A0 =A0 =A0 =A0 =A0 =
|
+--------------+------------------------------------------+- ---------+-----=
----------------------+---------------+--------------------- ---+
| no_image.jpg | Financial Intelligence for Entrepreneurs |=A0 =A0 =A0 =A0 =
=A0 | Karen=A0 Berman,Joe=A0 Knight | 9781422119150 | Harvard Business Pres=
s |
+--------------+------------------------------------------+- ---------+-----=
----------------------+---------------+--------------------- ---+
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 onl=
y. Why is this?


# Now is doesn't work.=20

mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_=
concat(concat(fname,' ',initial,' ',lname)) as authors, pkisbn, publisher F=
ROM book INNER JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JO=
IN module_book on=A0   =A0pkisbn =3D fkbook_isbn INNER JOIN module on f=
kmodule_nr =3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER =
JOIN author on fkauthor_id =3D pkauthor_id;=A0 =A0 =A0 =A0 =A0 =A0 =A0 +---=
-----------+--------------------------------------------+--- -------+-------=
------------------------------------------------------------ ---+-----------=
----+-------------------------------+
| image=A0 =A0 =A0 =A0 | title=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle | authors=A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 | pkisbn=A0 =A0 =A0 =A0 | publisher=A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0   =A0|
+--------------+-------------------------------------------- +----------+---=
------------------------------------------------------------ -------+-------=
--------+-------------------------------+
| no_image.jpg | The Origin and Evolution of New Businesses |=A0 =A0 =A0 =
=A0 =A0 | Amar V. Bhide,Eddie=A0 McLaney,Peter=A0 Atrill,Karen=A0 Berman,Jo=
e=A0 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_au=
thor on pkauthor_id =3D fkauthor_id) as authors, pkisbn, publisher FROM boo=
k INNER JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JOIN modu=
le_book on=A0 =A0 pkisbn =3D fkbook_isbn INNER JOIN module on fkmodule_nr =
=3D module_nr INNER JOIN book_author on pkisbn =3D fkisbn INNER JOIN author=
on fkauthor_id =3D pkauthor_id;



Tables from the database.

2 rows in set (0.00 sec) mysql> select * from author;
+-------------+-----------+---------+----------+
| pkauthor_id | fname=A0   =A0| initial | lname=A0 =A0 |
+-------------+-----------+---------+----------+
|=A0 =A0 =A0 =A0   =A01 | Karen=A0   =A0|=A0 =A0 =A0   =A0| Ber=
man  =A0|
|=A0 =A0 =A0 =A0   =A02 | Joe=A0 =A0   =A0|=A0 =A0 =A0   =A0| K=
night  =A0|
|=A0 =A0 =A0 =A0   =A03 | Eddie=A0   =A0|=A0 =A0 =A0   =A0| McL=
aney=A0 |
|=A0 =A0 =A0 =A0   =A04 | Peter=A0   =A0|=A0 =A0 =A0   =A0| Atr=
ill  =A0|
|=A0 =A0 =A0 =A0   =A05 | Thomas=A0 =A0 | H.=A0 =A0 =A0 | Byers=A0 =A0 =
|
|=A0 =A0 =A0 =A0   =A06 | Richard  =A0| C.=A0 =A0 =A0 | Dorf=A0 =A0=
  |
|=A0 =A0 =A0 =A0   =A07 | Andrew=A0 =A0 | J.=A0 =A0 =A0 | Nelson  =
=A0|
|=A0 =A0 =A0 =A0   =A09 | Christian | S=A0 =A0   =A0| Albright |
|=A0 =A0 =A0 =A0 =A0 10 | Wayne=A0   =A0| L.=A0 =A0 =A0 | Winston=A0 |
|=A0 =A0 =A0 =A0 =A0 11 | Amar=A0 =A0 =A0 | V.=A0 =A0 =A0 | Bhide=A0 =A0 |
+-------------+-----------+---------+----------+
10 rows in set (0.00 sec)


mysql> select pkisbn, fkpublisher_id, title, subtitle from book;
+---------------+----------------+-------------------------- ---------------=
---+-------------------------+
| pkisbn=A0 =A0 =A0 =A0 | fkpublisher_id | title=A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | subtitle=A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 |
+---------------+----------------+-------------------------- ---------------=
---+-------------------------+
| 9780071289214 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 1 | Technology Ventures=A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | From Idea to Enterprise |
| 9780131365483 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 8 | Computer Networking=A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | Top-Down Approach=A0 =A0   =
=A0|
| 9780195170313 |=A0 =A0 =A0 =A0 =A0   =A010 | The Origin and Evolution=
of New Businesses |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0   =A0|
| 9780273733652 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 5 | Accounting=A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0   =A0| An Introduction=A0 =
=A0 =A0   =A0|
| 9780324663464 |=A0 =A0 =A0 =A0 =A0   =A012 | Management Science Model=
ing, Revised=A0 =A0   =A0| International Edition  =A0|
| 9781422119150 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 3 | Financial Intelligence for=
Entrepreneurs  =A0|=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0   =
=A0|
+---------------+----------------+-------------------------- ---------------=
---+-------------------------+
6 rows in set (0.00 sec)

mysql> select pkpublisher_id, publisher from publisher;
+----------------+----------------------------------+
| pkpublisher_id | publisher=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
|
+----------------+----------------------------------+
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 1 | McGraw-Hill=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 |
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 3 | Harvard Business Press=A0 =A0 =A0 =A0 =A0=
  |
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 4 | Harper Business New York=A0 =A0 =A0   =
=A0|
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 5 | FT Prentice Hall=A0 =A0 =A0 =A0 =A0 =A0 =
=A0   =A0|
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 6 | Pitman London=A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 |
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 7 | Sams=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0   =A0|
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 8 | Pearson=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 |
|=A0 =A0 =A0 =A0 =A0 =A0 =A0 9 | Penguin=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 |
|=A0 =A0 =A0 =A0 =A0   =A010 | Oxford University Press - USA=A0 =A0 |
|=A0 =A0 =A0 =A0 =A0   =A011 | Oxford University Press - UK=A0   =
=A0|
|=A0 =A0 =A0 =A0 =A0   =A012 | Cengage Learning - South-Western |
+----------------+----------------------------------+
11 rows in set (0.00 sec)

mysql> select * from book_author;
+-------------+---------------+
| fkauthor_id | fkisbn=A0 =A0 =A0 =A0 |
+-------------+---------------+
|=A0 =A0 =A0 =A0   =A01 | 9781422119150 |
|=A0 =A0 =A0 =A0   =A02 | 9781422119150 |
|=A0 =A0 =A0 =A0   =A03 | 9780273733652 |
|=A0 =A0 =A0 =A0   =A04 | 9780273733652 |
|=A0 =A0 =A0 =A0   =A05 | 9780071289214 |
|=A0 =A0 =A0 =A0   =A06 | 9780071289214 |
|=A0 =A0 =A0 =A0   =A07 | 9780071289214 |
|=A0 =A0 =A0 =A0   =A09 | 9780324663464 |
|=A0 =A0 =A0 =A0 =A0 10 | 9780324663464 |
|=A0 =A0 =A0 =A0 =A0 11 | 9780195170313 |
+-------------+---------------+
10 rows in set (0.00 sec)

mysql> desc module;
+------------------+------------------------+------+-----+-- -------+-------=
---------+
| Field=A0 =A0 =A0 =A0 =A0 =A0 | Type=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0   =
=A0| Null | Key | Default | Extra=A0 =A0 =A0 =A0 =A0 |
+------------------+------------------------+------+-----+-- -------+-------=
---------+
| pkmodule_id=A0 =A0 =A0 | mediumint(10) unsigned | NO  =A0| PRI | NULL=
=A0 =A0 | auto_increment |
| module_nr=A0 =A0 =A0 =A0 | varchar(15)=A0 =A0 =A0 =A0 =A0 =A0 | NO  =
=A0| UNI | NULL=A0 =A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
| fkinstitution_id | mediumint(10) unsigned | NO  =A0| MUL | NULL=A0 =
=A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
| module_name=A0 =A0 =A0 | varchar(50)=A0 =A0 =A0 =A0 =A0 =A0 | NO  =A0=
|=A0   =A0| NULL=A0 =A0 |=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
+------------------+------------------------+------+-----+-- -------+-------=
---------+
4 rows in set (0.00 sec)

mysql> desc module_book;
+----------------+------------------------------------------ ----------+----=
--+-----+---------+-------+
| Field=A0 =A0 =A0 =A0 =A0 | Type=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0   =A0| Null | Key | Def=
ault | Extra |
+----------------+------------------------------------------ ----------+----=
--+-----+---------+-------+
| fkmodule_nr=A0 =A0 | varchar(15)=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | NO  =A0| PRI | NULL=A0 =A0 |=
=A0 =A0   =A0|
| fkbook_isbn=A0 =A0 | varchar(20)=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | NO  =A0| PRI | NULL=A0 =A0 |=
=A0 =A0   =A0|
| book_relavance | enum('Required','Core','Recommended','Background') | YES=
=A0 |=A0   =A0| NULL=A0 =A0 |=A0 =A0   =A0|
+----------------+------------------------------------------ ----------+----=
--+-----+---------+-------+
3 rows in set (0.00 sec)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dsb_akarmarkar@y=
ahoo.com


--0-1388225020-1305874059=:95043--

RE: Complex Query

------=_NextPart_000_0008_01CC16E1.9FC740B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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@yahoo.com]
Sent: 20 May 2011 07:48
To: 'Guido Schlenke'; mysql@lists.mysql.com; 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 wrote:


From: Mimi Cafe
Subject: RE: Complex Query
To: "'Guido Schlenke'" , 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_akarmarkar@yahoo.com




------=_NextPart_000_0008_01CC16E1.9FC740B0--

RE: Complex Query

------=_NextPart_000_0048_01CC16FA.0499B8C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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:mimicafe@googlemail.com]
Sent: 20 May 2011 11:33
To: 'Anupam Karmarkar'
Cc: 'Guido Schlenke'; mysql@lists.mysql.com
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:sb_akarmarkar@yahoo.com]
Sent: 20 May 2011 07:48
To: 'Guido Schlenke'; mysql@lists.mysql.com; 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 wrote:


From: Mimi Cafe
Subject: RE: Complex Query
To: "'Guido Schlenke'" , 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_akarmarkar@yahoo.com




------=_NextPart_000_0048_01CC16FA.0499B8C0--

Re: Complex Query

Hi Mimi,

try this

select count(*) from
( 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 );

Guido

"Mimi Cafe" schrieb im Newsbeitrag
news:004701cc16f1$a2d550c0$e87ff240$@com...

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:mimicafe@googlemail.com]
Sent: 20 May 2011 11:33
To: 'Anupam Karmarkar'
Cc: 'Guido Schlenke'; mysql@lists.mysql.com
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:sb_akarmarkar@yahoo.com]
Sent: 20 May 2011 07:48
To: 'Guido Schlenke'; mysql@lists.mysql.com; 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 wrote:


From: Mimi Cafe
Subject: RE: Complex Query
To: "'Guido Schlenke'" , 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_akarmarkar@yahoo.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Query

Hmm. Simply replacing the field list with count(*) should work, too. If you only need the count after having executed the select, I'm pretty sure there's something in the API that gives you that without a second query, although I'll be buggered if I can remember right now.

----- Original Message -----
> From: "Guido Schlenke"
> To: mysql@lists.mysql.com
> Sent: Friday, 20 May, 2011 9:04:32 PM
> Subject: Re: Complex Query
>
> Hi Mimi,
>
> try this
>
> select count(*) from
> ( 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 );
>
> Guido
>
> "Mimi Cafe" schrieb im Newsbeitrag
> news:004701cc16f1$a2d550c0$e87ff240$@com...
>
> 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:mimicafe@googlemail.com]
> Sent: 20 May 2011 11:33
> To: 'Anupam Karmarkar'
> Cc: 'Guido Schlenke'; mysql@lists.mysql.com
> 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:sb_akarmarkar@yahoo.com]
> Sent: 20 May 2011 07:48
> To: 'Guido Schlenke'; mysql@lists.mysql.com; 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.
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Complex Query

Select count(*) from (select........) did not work. It says: "ERROR 1248 =
(42000): Every derived table must have its own alias" and I am not sure =
it really mean aliases.

Although "select found_rows();" works, I am trying to avoid it as the =
documentation says it may produce expected result - especially after =
running a query with MySQL limit clause.

Mimi

=3D> -----Original Message-----
=3D> From: Johan De Meersman [mailto:vegivamp@tuxera.be]
=3D> Sent: 20 May 2011 21:16
=3D> To: Guido Schlenke
=3D> Cc: mysql@lists.mysql.com
=3D> Subject: Re: Complex Query
=3D>=20
=3D> Hmm. Simply replacing the field list with count(*) should work, =
too.
=3D> If you only need the count after having executed the select, I'm
=3D> pretty sure there's something in the API that gives you that =
without a
=3D> second query, although I'll be buggered if I can remember right =
now.
=3D>=20
=3D> ----- Original Message -----
=3D> > From: "Guido Schlenke"
=3D> > To: mysql@lists.mysql.com
=3D> > Sent: Friday, 20 May, 2011 9:04:32 PM
=3D> > Subject: Re: Complex Query
=3D> >
=3D> > Hi Mimi,
=3D> >
=3D> > try this
=3D> >
=3D> > select count(*) from
=3D> > ( SELECT module_nr, IFNULL(image,'no_image.jpg'),title, =
subtitle,
=3D> > group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
=3D> >
=3D> > publisher,publication_year, edition, cover_type, pages FROM =
book
=3D> > INNER
=3D> > JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JOIN
=3D> > module_book on
=3D> >
=3D> > pkisbn =3D fkbook_isbn INNER JOIN module on fkmodule_nr =3D
=3D> module_nr
=3D> > INNER
=3D> > JOIN book_author on pkisbn =3D fkisbn INNER JOIN author on =
fkauthor_id
=3D> > =3D
=3D> > pkauthor_id
=3D> >
=3D> > WHERE module_nr =3D ? group by =
IFNULL(image,'no_image.jpg'),title,
=3D> > subtitle, pkisbn, publisher );
=3D> >
=3D> > Guido
=3D> >
=3D> > "Mimi Cafe" schrieb im Newsbeitrag
=3D> > news:004701cc16f1$a2d550c0$e87ff240$@com...
=3D> >
=3D> > Hi
=3D> >
=3D> >
=3D> >
=3D> > I now need to determine the number of rows returned by this query
=3D> > below.
=3D> > Wrapping it within SELECT COUNT did not work as expected.
=3D> >
=3D> >
=3D> >
=3D> >
=3D> >
=3D> > SELECT module_nr, IFNULL(image,'no_image.jpg'),title, =
subtitle,
=3D> > group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
=3D> >
=3D> > publisher,publication_year, edition, cover_type, pages FROM =
book
=3D> > INNER
=3D> > JOIN publisher on pkpublisher_id =3D fkpublisher_id INNER JOIN
=3D> > module_book on
=3D> >
=3D> > pkisbn =3D fkbook_isbn INNER JOIN module on fkmodule_nr =3D
=3D> module_nr
=3D> > INNER
=3D> > JOIN book_author on pkisbn =3D fkisbn INNER JOIN author on =
fkauthor_id
=3D> > =3D
=3D> > pkauthor_id
=3D> >
=3D> > WHERE module_nr =3D ? group by =
IFNULL(image,'no_image.jpg'),title,
=3D> > subtitle, pkisbn, publisher;
=3D> >
=3D> >
=3D> >
=3D> > Mimi
=3D> >
=3D> >
=3D> >
=3D> > From: Mimi Cafe [mailto:mimicafe@googlemail.com]
=3D> > Sent: 20 May 2011 11:33
=3D> > To: 'Anupam Karmarkar'
=3D> > Cc: 'Guido Schlenke'; mysql@lists.mysql.com
=3D> > Subject: RE: Complex Query
=3D> >
=3D> >
=3D> >
=3D> > Hi guys
=3D> >
=3D> >
=3D> >
=3D> > That's cool! It looks like my query was good except that I miss =
the
=3D> > "group
=3D> > by". Now I only had to remove the "as image" from the grouping
=3D> below
=3D> > and it
=3D> > works fine.
=3D> >
=3D> >
=3D> >
=3D> > Thanks guys
=3D> >
=3D> >
=3D> >
=3D> > Mimi
=3D> >
=3D> >
=3D> >
=3D> > From: Anupam Karmarkar [mailto:sb_akarmarkar@yahoo.com]
=3D> > Sent: 20 May 2011 07:48
=3D> > To: 'Guido Schlenke'; mysql@lists.mysql.com; Mimi Cafe
=3D> > Subject: RE: Complex Query
=3D> >
=3D> >
=3D> >
=3D> >
=3D> > Hi Guido,
=3D> >
=3D> > You need to add group by in your query get desire result
=3D> >
=3D> > SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
=3D> > group_concat(concat(fname,' ',initial,' ',lname)) as author, =
pkisbn,
=3D> > publisher FROM book INNER JOIN publisher on pkpublisher_id =3D
=3D> > fkpublisher_id
=3D> > INNER JOIN module_book on pkisbn =3D fkbook_isbn INNER JOIN =
module
=3D> > on
=3D> > fkmodule_nr =3D module_nr INNER JOIN book_author on pkisbn =3D =
fkisbn
=3D> > INNER JOIN
=3D> > author on fkauthor_id =3D pkauthor_id
=3D> > WHERE module_nr =3D 'MSING0010'
=3D> > group by IFNULL(image,'no_image.jpg') as image,title, subtitle,
=3D> > pkisbn,
=3D> > publisher
=3D> >
=3D> > It should give desire result. As group concat will return group =
by 1
=3D> > if you
=3D> > dont specify group by, which will return only one row, to get =
result
=3D> > you
=3D> > need to specify group by as given in above query.
=3D> >
=3D>=20
=3D> --
=3D> Bier met grenadyn
=3D> Is als mosterd by den wyn
=3D> Sy die't drinkt, is eene kwezel
=3D> Hy die't drinkt, is ras een ezel
=3D>=20
=3D> --
=3D> MySQL General Mailing List
=3D> For list archives: http://lists.mysql.com/mysql
=3D> To unsubscribe:
=3D> http://lists.mysql.com/mysql?unsub=3Dmimicafe@gmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Complex Query

I meant it may produce unexpected result.

Mimi

=> -----Original Message-----
=> From: Mimi Cafe [mailto:mimicafe@googlemail.com]
=> Sent: 20 May 2011 22:52
=> To: 'Johan De Meersman'; 'Guido Schlenke'
=> Cc: mysql@lists.mysql.com
=> Subject: RE: Complex Query
=>
=> Select count(*) from (select........) did not work. It says: "ERROR
=> 1248 (42000): Every derived table must have its own alias" and I am
=> not sure it really mean aliases.
=>
=> Although "select found_rows();" works, I am trying to avoid it as the
=> documentation says it may produce expected result - especially after
=> running a query with MySQL limit clause.
=>
=> Mimi
=>
=> => -----Original Message-----
=> => From: Johan De Meersman [mailto:vegivamp@tuxera.be]
=> => Sent: 20 May 2011 21:16
=> => To: Guido Schlenke
=> => Cc: mysql@lists.mysql.com
=> => Subject: Re: Complex Query
=> =>
=> => Hmm. Simply replacing the field list with count(*) should work,
=> too.
=> => If you only need the count after having executed the select, I'm
=> => pretty sure there's something in the API that gives you that
=> without a
=> => second query, although I'll be buggered if I can remember right
=> now.
=> =>
=> => ----- Original Message -----
=> => > From: "Guido Schlenke"
=> => > To: mysql@lists.mysql.com
=> => > Sent: Friday, 20 May, 2011 9:04:32 PM
=> => > Subject: Re: Complex Query
=> => >
=> => > Hi Mimi,
=> => >
=> => > try this
=> => >
=> => > select count(*) from
=> => > ( 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 );
=> => >
=> => > Guido
=> => >
=> => > "Mimi Cafe" schrieb im Newsbeitrag
=> => > news:004701cc16f1$a2d550c0$e87ff240$@com...
=> => >
=> => > 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:mimicafe@googlemail.com]
=> => > Sent: 20 May 2011 11:33
=> => > To: 'Anupam Karmarkar'
=> => > Cc: 'Guido Schlenke'; mysql@lists.mysql.com
=> => > 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:sb_akarmarkar@yahoo.com]
=> => > Sent: 20 May 2011 07:48
=> => > To: 'Guido Schlenke'; mysql@lists.mysql.com; 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.
=> => >
=> =>
=> => --
=> => Bier met grenadyn
=> => Is als mosterd by den wyn
=> => Sy die't drinkt, is eene kwezel
=> => Hy die't drinkt, is ras een ezel
=> =>
=> => --
=> => MySQL General Mailing List
=> => For list archives: http://lists.mysql.com/mysql
=> => To unsubscribe:
=> => http://lists.mysql.com/mysql?unsub=mimicafe@gmail.com
=>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Query

Heh. The parser is pointing out a simple syntax oversight, yes. The correct syntax for that is select ... from (subselect) aliasname;

----- Original Message -----
> From: "Mimi Cafe"
> To: "Johan De Meersman" , "Guido Schlenke"
> Cc: mysql@lists.mysql.com
> Sent: Friday, 20 May, 2011 11:52:14 PM
> Subject: RE: Complex Query
>
> Select count(*) from (select........) did not work. It says: "ERROR
> 1248 (42000): Every derived table must have its own alias" and I am
> not sure it really mean aliases.
>
> Although "select found_rows();" works, I am trying to avoid it as the
> documentation says it may produce expected result - especially after
> running a query with MySQL limit clause.
>
> Mimi
>
> => -----Original Message-----
> => From: Johan De Meersman [mailto:vegivamp@tuxera.be]
> => Sent: 20 May 2011 21:16
> => To: Guido Schlenke
> => Cc: mysql@lists.mysql.com
> => Subject: Re: Complex Query
> =>
> => Hmm. Simply replacing the field list with count(*) should work,
> too.
> => If you only need the count after having executed the select, I'm
> => pretty sure there's something in the API that gives you that
> without a
> => second query, although I'll be buggered if I can remember right
> now.
> =>
> => ----- Original Message -----
> => > From: "Guido Schlenke"
> => > To: mysql@lists.mysql.com
> => > Sent: Friday, 20 May, 2011 9:04:32 PM
> => > Subject: Re: Complex Query
> => >
> => > Hi Mimi,
> => >
> => > try this
> => >
> => > select count(*) from
> => > ( 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 );
> => >
> => > Guido
> => >
> => > "Mimi Cafe" schrieb im Newsbeitrag
> => > news:004701cc16f1$a2d550c0$e87ff240$@com...
> => >
> => > 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:mimicafe@googlemail.com]
> => > Sent: 20 May 2011 11:33
> => > To: 'Anupam Karmarkar'
> => > Cc: 'Guido Schlenke'; mysql@lists.mysql.com
> => > 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:sb_akarmarkar@yahoo.com]
> => > Sent: 20 May 2011 07:48
> => > To: 'Guido Schlenke'; mysql@lists.mysql.com; 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.
> => >
> =>
> => --
> => Bier met grenadyn
> => Is als mosterd by den wyn
> => Sy die't drinkt, is eene kwezel
> => Hy die't drinkt, is ras een ezel
> =>
> => --
> => MySQL General Mailing List
> => For list archives: http://lists.mysql.com/mysql
> => To unsubscribe:
> => http://lists.mysql.com/mysql?unsub=mimicafe@gmail.com
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complex Query

>>>> 2011/05/21 00:22 +0200, Johan De Meersman >>>>
Heh. The parser is pointing out a simple syntax oversight, yes. The correct syntax for that is select ... from (subselect) aliasname;
<<<<<<<<
And that is required whether the alias is used or not. I at those times have been tripped up by this requirement when I had no use for the alias.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Complex Query

Bizarre! That worked, but it appears the alias is not used.

Thanks

Mimi

=3D> -----Original Message-----
=3D> From: Halï¿=BDsz Sï¿=BDndor [mailto:hsv@tbbs.net]
=3D> Sent: 21 May 2011 01:48
=3D> To: Johan De Meersman
=3D> Cc: Mimi Cafe; mysql@lists.mysql.com; Johan De Meersman; Guido
=3D> Schlenke
=3D> Subject: Re: Complex Query
=3D>=20
=3D> >>>> 2011/05/21 00:22 +0200, Johan De Meersman >>>>
=3D> Heh. The parser is pointing out a simple syntax oversight, yes. The
=3D> correct syntax for that is select ... from (subselect) aliasname;
=3D> <<<<<<<<
=3D> And that is required whether the alias is used or not. I at those
=3D> times have been tripped up by this requirement when I had no use =
for
=3D> the alias.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg