Re: [NEWBIE] Help with a simple query

"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

There is no problem, IMO, or you are specifying it incorrectly. RDBMS store sets, and sets are represented as tables. A view is again a set, and is represented as a table. If you have multiple authors, so you will have multiple tuples (author, title) with an indentical author. Consequently you have multiple rows.
Apparently you don't want to display them in that form, then you would need to write a stored procedure to display them correctly.

Sybrand Bakker
Senior Oracle DBA Received on Thu Jul 29 2004 - 13:01:08 CEST