List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:May 19 2011 10:43am
Subject:RE: Complex Query
View as plain text  
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@stripped]
=> 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@stripped]
=> Sent: 18 May 2011 22:23
=> To: mysql@stripped
=> 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.

Thread
Complex QueryMimi Cafe19 May
  • Re: Complex QueryClaudio Nanni19 May
  • Re: Complex Queryhsv19 May
RE: Complex QueryMimi Cafe19 May
  • Re: Complex QueryGuido Schlenke19 May
  • Re: Complex QueryGuido Schlenke19 May
    • RE: Complex QueryMimi Cafe20 May
      • RE: Complex QueryAnupam Karmarkar20 May
        • RE: Complex QueryMimi Cafe20 May
          • RE: Complex QueryMimi Cafe20 May
            • Re: Complex QueryGuido Schlenke20 May
              • Re: Complex QueryJohan De Meersman20 May
                • RE: Complex QueryMimi Cafe20 May
                  • RE: Complex QueryMimi Cafe20 May
                  • Re: Complex QueryJohan De Meersman21 May
                    • Re: Complex Queryhsv21 May
                      • RE: Complex QueryMimi Cafe21 May