473,568 Members | 3,038 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

[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=BOO K_AUTHOR.Title AND
BOOK_AUTHOR.A_N ame=AUTHOR.A_Na me);

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 1321
"castor." <ca************ ****@fastwebnet .itwrote in message news:<Go******* ************@to rnado.fastwebne t.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=BOO K_AUTHOR.Title AND
BOOK_AUTHOR.A_N ame=AUTHOR.A_Na me);

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******** ***********@tor nado.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=BOO K_AUTHOR.Title AND
BOOK_AUTHOR.A_N ame=AUTHOR.A_Na me);

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2170
cassbiz
by: cassbiz | last post by:
I am having a problem getting a simple form to work. I keep getting an error in the select statement. Error performing query: Unknown column '$zip' in 'where clause' below is my code and it is driving me nuts. What am I doing wrong? Read the Posting Guidelines before you post anything!! Edited for eadability reasons. <form...
6
1770
by: François Dejaeghere | last post by:
Hello dear readers, I have 2 tables, let's say first table: "Appointment", which has 3 fields (I've simplified the schema for better understanding) ID as GUID (the key) Length as Float TaskID as GUID ((external key) The second table is called "Task" and has 2 fields (again, simplified schema) ID as GUID (key)
1
1210
by: Raycaster | last post by:
I'm 3/4 finished 2 simple VB apps for a upcoming silent auction being held at my children's school. Very Simple - This is what it does: 1st app allows input and builds a text database Unit ID (Done before Auction) Unit Description (Done before Auction) Unit Donor (Done before Auction) Unit Value (Done before Auction)
16
1865
by: Raxit | last post by:
Hi, i was reading/learning some hello world program in python. I think its very simillar to Java/C++/C#. What's different (except syntax) ? what can i do easily with python which is not easy in c++/java !? Tnx, Raxit
7
1506
by: idiolect | last post by:
Hi all - Sorry to plague you with another newbie question from a lurker. Hopefully, this will be simple. I have a list full of RGB pixel values read from an image. I want to test each RGB band value per pixel, and set it to something else if it meets or falls below a certain threshold - i.e., a Red value of 0 would be changed to 50. ...
2
10142
by: Eraser | last post by:
Hello, I'm just starting to learn PL/SQL. To get my feet wet, I'm trying to write a simple stored procedure that takes some values as parameters, and inserts those values into a table. For some reason my simple procedure is not working, I'm probably missing something simple. Here is how I'm trying to create this procedure: CREATE OR...
5
2252
by: Chris Cowles | last post by:
I use an application that uses Oracle 8.1.7. All functions of the application are completed with calls to stored procedures. A data entry error occurred that caused thousands of records to be created with a consistent error in a single field. I can identify those easily records with a select statement. I'd *really* rather not have to change...
13
390
by: Baldaris | last post by:
Show.php--This is for showing movie names available in database <?php include "auth.php"; $query="SELECT movie_id,movie_name FROM moviesite"; $result=mysql_query($query,$link) or die("Can't execute query" .mysql_error()); while($row=mysql_fetch_array($result)) { $movie_id=$row;
8
8544
by: danielb1952 | last post by:
I have created a form that uses the results of two queries: the first query is used to populate a combo box and then the selected value in the combo box is used as a parameter for the second query. This works fine. I have also created a report that displays the results of a third query, where the third query is identical to the second query...
0
7692
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7601
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8117
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7659
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7962
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6274
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5496
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5217
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2099
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.