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.pitc...@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:mimic...@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-mysq...@m.gmane.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
