471,316 Members | 1,299 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,316 software developers and data experts.

[NEWBIE] Help with a simple query

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
Jun 27 '08 #1
3 1266
"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
Jun 27 '08 #2
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

Jun 27 '08 #3
thanks for the answers, back to work now :)
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Raxit | last post: by
5 posts views Thread by Chris Cowles | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.