You get multiple lines for multi-author books because there are multiple row
combinations from the table that satisfy your query. In other words, you
are basically asking the question "which author wrote each book?" and for
those books there is more than one answer. You will need to write a
function (or some such thing) to retrieve the names of the authors into a
comma-delimited string (or whatever you want), and then use the function
instead of the a_name field. You may also have to use DISTINCT to get rid
of duplicates. Alternatively, you could change your query so that it just
displays the first author match for each book, but I doubt that would
satisfy your requirements.
--
Cheers,
Chris
___________________________________
Chris Leonard, The Database Guy
http://www.databaseguy.com
Brainbench MVP for Oracle Admin
http://www.brainbench.com
MCSE, MCDBA, OCP, CIW
___________________________________
"castor." <castorrr-removeME-_at_fastwebnet.it> wrote in message
news:GoYNc.102722$G%.42816_at_tornado.fastwebnet.it...
> hi all,
>
> i have two tables
>
> BOOK
> ------------------------------------
> CODE NOT NULL NUMBER
> TITLE VARCHAR2(45)
> YEAR NUMBER
>
>
> AUTHOR
> ------------------------------------
> A_NAME NOT NULL VARCHAR2(15)
> DESCRIPTION VARCHAR2(40)
>
> i had an n->n relation with title/a_name, so i created a mid entity:
>
> BOOK_AUTHOR
> ------------
> TITLE
> A_NAME
>
> After that i created the following view
>
> create view ALL as
> (select Code, BOOK.Title, AUTHOR.A_Name
> from BOOK, BOOK_AUTHOR, AUTHOR
> where (BOOK.Title=BOOK_AUTHOR.Title AND
> BOOK_AUTHOR.A_Name=AUTHOR.A_Name);
>
> So, where's my problem? When a book is written by multiple authors. when
> i select the view, multiple authors are put in multiple lines. Is there
> any way out?
>
> thx for help, cas
>
Received on Fri Jul 30 2004 - 12:55:20 CDT
