Use an ANSI outer join and use the NVL function to convert nulls to the string "N/A".
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>
SQL>
SQL> select supplier_name, nvl(description, 'N/A') AS description
2 from supplier left outer join product_supplier using (supplier_no)
3 left outer join product using (product_id);
SUPPLIER_NAME DESCRIPTION
-------------------------------------------------- ---------------------------------------------------------------------------
WWW Gift Supply Co. Oracle
BCD Gift Supply Co. Oracle
ABC Gift Supply Co. Oracle
WWW Gift Supply Co. SQL Server
BCD Gift Supply Co. SQL Server
ABC Gift Supply Co. SQL Server
B Gift Supply Co. N/A
R Gift Supply Co. N/A
P Gift Supply Co. N/A
W Gift Supply Co. N/A
R Gift Supply Co. N/A
D Gift Supply Co. N/A
XYZ Gift Supply Co. N/A
13 rows selected.
SQL>
SQL>
SQL>
SQL> drop table supplier;
Table dropped.
SQL> drop table product_supplier;
Table dropped.
SQL> drop table product;
Table dropped.
SQL>
SQL> --
Related examples in the same category