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=arch...@jab.org
