[NEWBIE] Help with a simple query

[castor.]

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

[sybrandb]

"castor." <ca****************@fastwebnet.itwrote in message news:<Go*******************@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

[Chris Leonard]

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." <ca****************@fastwebnet.itwrote in message
news:Go*******************@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

[castor.]

thanks for the answers, back to work now :)