Home » SQL & PL/SQL » SQL & PL/SQL » need to combine two tables
need to combine two tables [message #269309] Fri, 21 September 2007 11:03 Go to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Hi,
Any ideas as to how to create a view/table that will combine database columns from different tables?

A.ID,
A.Name,
A.qty as shippedQ,
A.desc
from t1 A, t2 B
where a.sid=b.sid
union
B.ID,
B.Name,
E.qty as returnedQ,
E.institute
from t3 C, t4 D, t5 E
where
c.sid= d.sid and
c.rid = e.rid

This does the union but I want a new table/view with the columns as id, name, site, shippedQ, returnedQ, desc, institute
(id, name, site should be a union of rows from the two tables)

How do I achieve this?
Re: need to combine two tables [message #269311 is a reply to message #269309] Fri, 21 September 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create table as <your query>

Regards
Michel
Re: need to combine two tables [message #269315 is a reply to message #269311] Fri, 21 September 2007 11:56 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Thanks Michel,
I may not have been very clear. I need columns from the union sql as seperate columns in my new table.

I need a new table NEWTABLE with columns-
id, name, site, sqty, institute, rtqty

FROM-

Table1.id,
table1.name,
table1.site,
table1.Sqty
union
table2.id,
table2.name,
table2.institute,
table3.rtqty


The create table with union will not do that.
How do I achieve this?
Re: need to combine two tables [message #269316 is a reply to message #269315] Fri, 21 September 2007 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First find a query that fit your requirements and the use CTAS.

Regards
Michel
Re: need to combine two tables [message #269318 is a reply to message #269316] Fri, 21 September 2007 12:15 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Hi Michel,
I did not understand your last post?
My two examples are my queries- I simplified in the second post.

What is a CTA?
Thanks
Re: need to combine two tables [message #269319 is a reply to message #269309] Fri, 21 September 2007 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Create Table AS (CTAS)
Re: need to combine two tables [message #269320 is a reply to message #269318] Fri, 21 September 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your 2 queries does not give ONE query.
There is no relation between the 2, so can't have the final result.
So FIRST find a query that gives the result you want then use CTAS.

Regards
Michel
Re: need to combine two tables [message #269322 is a reply to message #269319] Fri, 21 September 2007 12:33 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Thanks again,
I can only create inline views/derived tables as i am using Business objects designer tool for this.
My original issue still remains unresolved, how to get all the different columns as seperate columns from the 2 tables (not like the way union does)??
Any light on this matter is greatly appreciated.
Re: need to combine two tables [message #269326 is a reply to message #269322] Fri, 21 September 2007 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My original issue still remains unresolved

You were still not be able to express it.
What is the relation between A,B and C,D,E?

Regards
Michel
Re: need to combine two tables [message #269331 is a reply to message #269326] Fri, 21 September 2007 13:29 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
Michel,
These being oracle application tables, do not have a real relationship.
t1 is shipment data, t3 is returns data. Packages are shipped to site, some packages may have returns. Not all shipments may have returns.
t2 and t3 are the same table - a look up table for ID (package ids).
t5 has a few fields for returns information like the institute.

t5 has a many to one relationship with t4,
t4 has a many to one relations with t2/3
t2/3 has a one to many relation with t1

Hope this clears things a little bit.


A.ID,
A.Name,
A.qty as shippedQ,
A.desc
from t1 A, t2 B
where a.sid=b.sid
union
B.ID,
B.Name,
E.qty as returnedQ,
E.institute
from t3 C, t4 D, t5 E
where
c.sid= d.sid and
c.rid = e.rid

Re: need to combine two tables [message #269337 is a reply to message #269331] Fri, 21 September 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now you have alll the relation then write a query that express them.
You already joined t1 and t2, and t3, t4, t5 now express the relation between the other tables.
There is no union.

Regards
Michel
Re: need to combine two tables [message #269346 is a reply to message #269337] Fri, 21 September 2007 15:14 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
oK, lets put it this way,
First, I need to write a report which first prompts the user for certain criteria-
Enter Site,
Enter IDs.

These two fields are in the t1 and t3 tables. For prompting the user, he needs to be presented with a List of all possible site and ID values coming from both the tables t1 and t3. ((hence the union idea))

Second, on the report, I need to show all the columns- site, ids, Qshipped, Qreturned for the site and IDs selected by the user. ((hence the union idea does not work here))

Hence I was posing the question. Any ideas how to resolve this?
Re: need to combine two tables [message #269351 is a reply to message #269346] Fri, 21 September 2007 15:20 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Then for the first thing you can use the union concept and for the second criteria why not you using join concept insetad of union because values comes for the both table.

Cheers
Soumen
Re: need to combine two tables [message #269371 is a reply to message #269351] Fri, 21 September 2007 17:13 Go to previous messageGo to next message
rachana
Messages: 27
Registered: August 2003
Junior Member
wish it was that simple (union and then join)
Cool
Re: need to combine two tables [message #269397 is a reply to message #269346] Sat, 22 September 2007 01:06 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let's put it this way: follow a SQL course.
Your question is not difficult (and others told it like me), you just have to join the 2 parts.
The join criteria depend on the relations between tables that only YOU know.

Regards
Michel

[Updated on: Sat, 22 September 2007 01:06]

Report message to a moderator

Previous Topic: problem while using select statememt
Next Topic: UTL_SMTP Package
Goto Forum:
  


Current Time: Tue Dec 06 11:56:00 CST 2016

Total time taken to generate the page: 0.12435 seconds