Home » SQL & PL/SQL » SQL & PL/SQL » problem on creation of table
problem on creation of table [message #200710] Wed, 01 November 2006 00:57 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello all,
i m having two table.
i want to create a table which is formed by the joing of two table but i dnt want to use cartesian join.

SQL>select * from BOOKSHELF_AUTHOR;

TITLE AUTHORNAME
---------------------------------------- ------------------------------
to kill a mocking bird harper lee
wonderfull life stephen jay gould
innumeracy john allen paulos
kierkegaard anthology robert bretall
kierkegaard anthology soren kierkegaard
anne of green gables lucy maud montgomery
good dog, carl alexandra day
letters and papers from prison dietrich bonhoeffer

8 rows selected.

SQL >select * from category;

CATEGORYNAME PARENTCATE SUBCATEGORY
--------------- ---------- ---------------
adultref adult reference
adiltfic adult fiction
adultnf adult nonfiction
childrenpic children picture book
childrenfic childern fiction
childernnf children nonfiction
adultref adultFREE reference
adultmai adultless bigger

8 rows selected.

I want the required result as--

TITLE AUTHORNAME CATEGORYNAME
------------------------------ ------------------------- ------------ ----------
to kill a mocking bird harper lee adultfic
wonderfull life stephen jay gould adultnf
innumeracy john allen paulos adultnf
kierkegaard anthology robert bretall adultref
kierkegaard anthology soren kierkegaard
anne of green gables lucy maud montgomery childrenpic
good dog, carl alexandra day childrenfic
letters and papers from prison dietrich bonhoeffer adultnf

8 rows selected

i have tried it by cartesian join which gaves me 64 rows selected.

please do modify on my query-
create table bookshelf as (select b.title, b.authorname, c.category from bookshelf_author b, category c);

with regards
ashish
Re: problem on creation of table [message #200725 is a reply to message #200710] Wed, 01 November 2006 01:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you would need would be a column in the table BOOKSHELF_AUTHOR that referenced a value from the column CATEGORYNAME in the table CATEGORY.

With your current data structure, there is simply no connection between the two tables, and what you ask cannot be done.

Ask yourself - from the data you have presented us with in BOOKSHELF_AUTHOR and CATEGORY, how can I determine that To Kill A Mockingbird is AdultFic, and not ChildrenPic.

You need to do this:
ALTER TABLE bookshelf_author ADD (category  varchar2(30);
ALTER TABLE category ADD (CONSTRAINT category_pk primary key (categoryname);
ALTER TABLE bookshelf_author ADD (CONSTRAINT book_auth_cat_fk FOERIGN KEY (category) REFERENCES category(categoryname);
Then you need to make sure that every row in BOOKSHELF_AUTHOR has a value in the new column.

Then you can use a query like this to create your table:
create table bookshelf as (select b.title, b.authorname, c.category 
                           from bookshelf_author b, category c
                           where b.category = c.categoryname);

Re: problem on creation of table [message #200732 is a reply to message #200710] Wed, 01 November 2006 02:05 Go to previous message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanxx JRowbottom

i got my answer.

with regards
ashish
Previous Topic: Query
Next Topic: grouping query / Can someone help me writing sql (merged 4 threads)
Goto Forum:
  


Current Time: Sun Dec 11 04:16:25 CST 2016

Total time taken to generate the page: 0.08836 seconds