Bryan, Thanks. This is a good solution. I wish I had known this earlier. I ha ve other issues where this would work perfect.
But in this case, I am unable to utilized the Order By for this query in the way you described. I should have clearified this. There is a SCORE column that I am using to Order the results by. The example I provided in my original posing was simplified to reduce all the extra clutter. This SCORE column provides a relevance value to a search using Oracle's 8 i Intermedia Text Search Engine. Your solution has provided me with another idea. Maybe I could write an Oracle Function that would return a delimited stri ng of all the authors into one column. For example: select b.bid, b.isbn, b.title, getAuthors( b.bid ) as Authors from book b where score > 0 order by score Does this appear to be a good approach. I am open to criticism, thoughts, and other ideas. I sincerely appreciate the response. Sincerely, Troy Bryan Love wrote: > put this in your SQL statement: > ORDER BY [bookName?],[authorName?] > > You'll still get two records for one book, but you can control the outp ut > with CF like so: > > <cfoutput query="myBooks" group="[bookName?]"> > #bookName# > authors: <cfoutput><u>#authorName#</u> </cfoutput><br><br> > </cfoutput> > > the outer loop will only list each book once while the inner loop will > iterate over the book's authors > > +-----------------------------------------------+ > Bryan Love > Macromedia Certified Professional > Internet Application Developer > Database Analyst > Telecommunication Systems > [EMAIL PROTECTED] > +-----------------------------------------------+ > > "...'If there must be trouble, let it be in my day, that my child may h ave > peace'..." > - Thomas Paine, The American Crisis > > -----Original Message----- > From: Troy Simpson [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 09, 2002 4:08 PM > To: CF-Talk > Subject: Weird Join (o8i) > > 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. A s > 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 > > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc 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
