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.
Troy
--
Troy Simpson | North Carolina State University
NCSU Libraries | Campus Box 7111 | Raleigh | North Carolina
ph.919.515.3855 | fax.919.513.3330
It is better to be hated for what you are
than to be loved for what you are not.
-- Andre Gide
______________________________________________________________________
Get Your Own 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=coldfusionb
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
