An inner join to
SQL>
SQL>
SQL> create table product(
2 product_id integer primary key
3 ,price number(7,2)
4 ,description varchar2(75)
5 ,onhand number(5,0)
6 ,reorder number(5,0)
7 ,supplier_no integer
8 );
Table created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,'Oracle',100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,'SQL Server',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,'MySQL',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,'DB2',50,10);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,'Java',100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,'C++',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,'Javascript',3,5);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,'Ruby',null,null);
1 row created.
SQL>
SQL> select * from product;
PRODUCT_ID PRICE DESCRIPTION ONHAND REORDER SUPPLIER_NO
---------- ---------- --------------------------------------------------------------------------- ---------- ---------- -----------
1 2.5 Oracle 100 20
2 23 SQL Server
3 MySQL
4 1.5 DB2 50 10
5 10.5 Java 100 20
6 45 C++
7 19.99 Javascript 3 5
8 4.5 Ruby
8 rows selected.
SQL>
SQL> create table product_supplier(
2 product_id integer
3 ,supplier_no integer
4 ,price number(7,2)
5 ,primary key (product_id, supplier_no)
6 );
Table created.
SQL> insert into product_supplier values(1,10,2.25);
1 row created.
SQL> insert into product_supplier values(1,11,2.10);
1 row created.
SQL> insert into product_supplier values(1,12,2.85);
1 row created.
SQL> insert into product_supplier values(2,10,22.25);
1 row created.
SQL> insert into product_supplier values(2,11,22.00);
1 row created.
SQL> insert into product_supplier values(2,12,21.25);
1 row created.
SQL>
SQL> select * from product_supplier;
PRODUCT_ID SUPPLIER_NO PRICE
---------- ----------- ----------
1 10 2.25
1 11 2.1
1 12 2.85
2 10 22.25
2 11 22
2 12 21.25
6 rows selected.
SQL>
SQL> create table supplier(
2 supplier_no integer primary key
3 ,supplier_name varchar2(50)
4 ,address varchar(30)
5 ,city varchar(20)
6 ,state varchar2(2)
7 ,area_code varchar2(3)
8 ,phone varchar2(8)
9 );
Table created.
SQL> insert into supplier(supplier_no, supplier_name)values(10,'ABC Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(11,'BCD Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(12,'WWW Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(13,'XYZ Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(14,'R Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(15,'D Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(16,'B Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(17,'W Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(18,'P Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(19,'R Gift Supply Co.');
1 row created.
SQL>
SQL> select * from supplier;
SUPPLIER_NO SUPPLIER_NAME ADDRESS CITY ST ARE PHONE
----------- -------------------------------------------------- ------------------------------ -------------------- -- --- --------
10 ABC Gift Supply Co.
11 BCD Gift Supply Co.
12 WWW Gift Supply Co.
13 XYZ Gift Supply Co.
14 R Gift Supply Co.
15 D Gift Supply Co.
16 B Gift Supply Co.
17 W Gift Supply Co.
18 P Gift Supply Co.
19 R Gift Supply Co.
10 rows selected.
SQL>
SQL> select supplier_name,
2 description
3 from supplier s,product_supplier ps,product p
4 where s.supplier_no = ps.supplier_no
5 and ps.product_id = p.product_id;
SUPPLIER_NAME DESCRIPTION
-------------------------------------------------- ---------------------------------------------------------------------------
ABC Gift Supply Co. Oracle
BCD Gift Supply Co. Oracle
WWW Gift Supply Co. Oracle
ABC Gift Supply Co. SQL Server
SUPPLIER_NAME DESCRIPTION
-------------------------------------------------- ---------------------------------------------------------------------------
BCD Gift Supply Co. SQL Server
WWW Gift Supply Co. SQL Server
6 rows selected.
SQL>
SQL> drop table supplier;
Table dropped.
SQL> drop table product_supplier;
Table dropped.
SQL> drop table product;
Table dropped.
SQL> --
Related examples in the same category