List:General Discussion« Previous MessageNext Message »
From:Guido Schlenke Date:May 19 2011 3:09pm
Subject:Re: Complex Query
View as plain text  
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@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.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/mysql?unsub=1


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