Home » SQL & PL/SQL » SQL & PL/SQL » retrieve the least values from three tables and insert them into one table (Oracle 10G, MS Windows XP SP3)
retrieve the least values from three tables and insert them into one table [message #380371] Sun, 11 January 2009 07:46 Go to next message
raed_237
Messages: 19
Registered: August 2008
Junior Member
hi all,
hope you are doing well, here is the problem, i have three table, each one include 3 columns,like below

ICALL Table
Destination-----------------Code-----Price_Per_Minute
======================================================
Norway-Netcom Mobile-------449337--------0.1274
United Kingdom-Vodafone Mobile---------447777--------0.8134


IDT Table
Destination-----------------Code-----Price_Per_Minute
======================================================
Norway-Netcom -------449337--------0.1270
UK-Vodafone Mobile---------447777--------0.8135



TATA Table
Destination-----------------Code-----Price_Per_Minute
======================================================
Norway-Netcom Mobile-------449337--------0.1272
UK-Vodafone Mobile---------447777--------0.8138

so we have three table with this format and i need to generate one table from the three that contains the Cheapest Prices for each destination in the world, noticing that the destinations names are different in each one of the three tables as each price list is provided by a different company or carrier, so we can depend on the country code as it is valid for all the tables.

the new table should look something like this:

Destination----------Code-----Price_Per_Minute------Carrier_name
=============================================================
Norway-Netcom Mobile--449337------0.1270----IDT
UK-Vodafone Mobile--447777--------0.8134----ICALL

carrier name should refer to the table name that contains the cheapest price for a destination.

the table' names are:

1- TATA
2- IDT
3- ICALL

the name of the resulted table is not important, by the way each table contains more 10000 recoreds.

many thanks in advance
Re: retrieve the least values from three tables and insert them into one table [message #380373 is a reply to message #380371] Sun, 11 January 2009 07:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Do something like .. .

select Destination,Code,Price_Per_Minute,'ICALL' "CARRIER" from ICALL UNION
select Destination,Code,Price_Per_Minute,'IDT' "CARRIER" from IDT UNION
select Destination,Code,Price_Per_Minute,'TATA' "CARRIER" from TATA 


and complete the rest .

Smile
Rajuvan

[Updated on: Sun, 11 January 2009 07:58]

Report message to a moderator

Re: retrieve the least values from three tables and insert them into one table [message #380378 is a reply to message #380373] Sun, 11 January 2009 08:09 Go to previous messageGo to next message
raed_237
Messages: 19
Registered: August 2008
Junior Member
would this query provide a table with the smallest prices per each destination from the three tables ???
if so, do u mind explain it to me
Re: retrieve the least values from three tables and insert them into one table [message #380379 is a reply to message #380371] Sun, 11 January 2009 08:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No . My query will not give you the exact result you want. Its just a hint .

Thats why i told you ,

Quote:
and complete the rest


Smile
Rajuvan.
Re: retrieve the least values from three tables and insert them into one table [message #380380 is a reply to message #380379] Sun, 11 January 2009 08:21 Go to previous messageGo to next message
raed_237
Messages: 19
Registered: August 2008
Junior Member
thx the thing is i am not an oracle expert and i need to be done with this within 2 hours, thank u anyway
Re: retrieve the least values from three tables and insert them into one table [message #380382 is a reply to message #380380] Sun, 11 January 2009 08:57 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here is one way to do the job.

First, working environment:
SQL> CREATE TABLE icall (destination VARCHAR2(30), code NUMBER, price NUMBER);

Table created.

SQL> CREATE TABLE idt (destination VARCHAR2(30), code NUMBER, price NUMBER);

Table created.

SQL> CREATE TABLE tata (destination VARCHAR2(30), code NUMBER, price NUMBER);

Table created.

SQL>
SQL> INSERT ALL
  2   INTO icall VALUES ('Norway-Netcom', 449337, 0.1274)
  3     INTO icall VALUES ('UK Vodafone', 447777, 0.8134)
  4     INTO idt VALUES ('Norway-Netcom', 449337, 0.1270)
  5     INTO idt VALUES ('UK Vodafone', 447777, 0.8135)
  6     INTO tata VALUES ('Norway-Netcom', 449337, 0.1272)
  7     INTO tata VALUES ('UK Vodafone', 447777, 0.8138)
  8  SELECT * FROM dual;

6 rows created.

SQL>

I'll now create a view which consists of data entered into all three tables, as well as additional column called "carrier" in order to know which records belong to which tables:
SQL> CREATE VIEW v_mobile AS
  2    SELECT destination, code, price, 'ICALL' carrier FROM icall
  3    UNION ALL
  4    SELECT destination, code, price, 'IDT' carrier FROM idt
  5    UNION ALL
  6    SELECT destination, code, price, 'TATA' carrier FROM tata;

View created.

SQL>

Now, solution:
- select a record with minimum price grouped by "destination" and "code" columns
- join that result with the "carrier" information:
SQL> CREATE TABLE lowest_price AS
  2  SELECT t2.destination, t2.code, t2.price, t3.carrier
  3  FROM (SELECT t1.destination, t1.code, MIN(t1.price) price
  4        FROM v_mobile t1
  5        GROUP BY t1.destination, t1.code
  6       ) t2,
  7       v_mobile t3
  8  WHERE t2.destination = t3.destination
  9    AND t2.code = t3.code
 10    AND t2.price = t3.price;

Table created.

SQL>

Finally, the result:
SQL> column price format 0.0000
SQL> SELECT * FROM lowest_price ORDER BY destination;

DESTINATION                          CODE   PRICE CARRI
------------------------------ ---------- ------- -----
Norway-Netcom                      449337  0.1270 IDT
UK Vodafone                        447777  0.8134 ICALL

SQL>

I guess that this could have been done in another way as well, but I hope it will help you solve the problem.
Re: retrieve the least values from three tables and insert them into one table [message #380383 is a reply to message #380382] Sun, 11 January 2009 09:05 Go to previous message
raed_237
Messages: 19
Registered: August 2008
Junior Member
thx Littlefoot,
just on time, much appreciated mate Smile
and thx again rajavu1
Previous Topic: what changes are to be made to sqlplus to show all the data on a single lin
Next Topic: How to Populate a fact table from dimension tables.Kindly reply.
Goto Forum:
  


Current Time: Wed Dec 07 10:34:24 CST 2016

Total time taken to generate the page: 0.16107 seconds