Troy,
Sorry for the late reply, but you can use this:
select
b.bid,
b.isbn,
b.title,
a.aid,
a.firstname,
a.lastname
from
book b,
author_book ab,
author a
where
b.bid = ab.bid
and
ab.aid = a.aid
and ab.aid = (
select min(aid)
from author_book ab2
where ab2.bid = ab.bid)
This will not list both authors, however. It'll only
list the one with the lowest aid.
--- Troy Simpson <[EMAIL PROTECTED]> wrote:
> Problem: I perform a Join and I get two records for
> a book with two
> authors. I do not really want two records. I would
> like to have one
> record for the book with both(or more) authors on
> the same row.
>
> Anyone run into something like this before? If so,
> how did you resolve
> it?
>
> Here is more information:
> There are two tables called AUTHOR and BOOK with a
> many-to-many
> relationship. The third table is an associative
> table called
> AUTHOR_BOOK. The table definitions are as follows:
>
/*==============================================================*/
> /* Database name: PHYSICALDATAMODEL_1 */
> /* DBMS name: ORACLE Version 8i2 (8.1.6) */
> /* Created on: 1/9/2002 6:24:16 PM */
>
/*==============================================================*/
>
/*==============================================================*/
> /* Table : AUTHOR */
>
/*==============================================================*/
> create table AUTHOR (
> AID NUMBER not null,
> FIRSTNAME VARCHAR2(35),
> LASTNAME VARCHAR(35),
> constraint PK_AUTHOR primary key (AID)
> )
> /
>
/*==============================================================*/
> /* Table : BOOK */
>
/*==============================================================*/
> create table BOOK (
> BID NUMBER not null,
> ISBN VARCHAR2(14),
> TITLE VARCHAR(50),
> constraint PK_BOOK primary key (BID)
> )
> /
>
/*==============================================================*/
> /* Table : AUTHOR_BOOK */
>
/*==============================================================*/
> create table AUTHOR_BOOK (
> BID NUMBER not null,
> AID NUMBER not null,
> constraint PK_AUTHOR_BOOK primary key (AID, BID),
> constraint FK_AUTHOR_B_REFERENCE_AUTHOR foreign key
> (AID)
> references AUTHOR (AID),
> constraint FK_AUTHOR_B_REFERENCE_BOOK foreign key
> (BID)
> references BOOK (BID)
> )
> /
> /************************************************/
> I run the following SQL statement:
> select
> b.bid,
> b.isbn,
> b.title,
> a.aid,
> a.firstname,
> a.lastname
> from
> book b,
> author_book ab,
> author a
> where
> b.bid = ab.bid
> and
> ab.aid = a.aid
>
> And I get the following results:
>
> BID ISBN TITLE
> AID
> FIRSTNAME LASTNAME
> ---------- --------------
> ------------------------------ ----------
> ---------- ---------------
> 1 0-07-212188-2 Oracle8i DBA Handbook
> 1
> Kevin Loney
> 1 0-07-212188-2 Oracle8i DBA Handbook
> 2
> Marlene Theriault
> 2 1-56592-578-5 Oracle SQL*Plus: The
> Definitiv 3
> Jonathan Gennick
> e Guide
>
> 3 1-56592-243-3 Perl Cookbook
> 4
> Tom Christiansen
> 3 1-56592-243-3 Perl Cookbook
> 5
> Nathan Torkington
>
> ************************************
>
> I really want to list one book with both(all)
> authors as one record. As
> you can see, if a book has two authors, I get two
> records for each
> author. Is this possible and if so how would I
> approach this.
>
>
> Thanks for your time and consideration.
=====
I-Lin Kuo
Macromedia CF5 Advanced Developer
Sun Certified Java 2 Programmer
__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
