Creating a view that combines data from multiple tables

A view that combines data from multiple tables enables you to show relevant information in multiple tables together. You can create a view that combines data from two or more tables by naming more than one table in the FROM clause.

In the following example procedure, the INVENTORY_LIST table contains a column of item numbers called ITEM_NUMBER and a column of item cost called UNIT_COST. These columns are joined with the ITEM_NUMBER column and the SUPPLIER_COST column of the SUPPLIERS table. A WHERE clause is used to limit the number of rows returned. The view contains only the item numbers for suppliers that can supply an item at lower cost than the current unit cost.

Use the following statement to create the view:

CREATE VIEW SAMPLECOLL.LOWER_COST AS
	SELECT SUPPLIER_NUMBER, A.ITEM_NUMBER,UNIT_COST, SUPPLIER_COST
		FROM SAMPLECOLL.INVENTORY_LIST A, SAMPLECOLL.SUPPLIERS B
		WHERE A.ITEM_NUMBER = B.ITEM_NUMBER
		AND UNIT_COST > SUPPLIER_COST

Run this statement:
```
SELECT *FROM SAMPLECOLL.LOWER_COST
```

The results look like this.

                                 Display Data
                                             Data width . . . . . . :      51
Position to line  . . . . .              Shift to column  . . . . . .
....+....1....+....2....+....3....+....4....+....5.
SUPPLIER_NUMBER  ITEM          UNIT   SUPPLIER_COST
                 NUMBER        COST
     1234        229740        1.50           1.00
     9988        153047       10.00           8.00
     2424        153047       10.00           9.00
     3366        303476        2.00           1.50
     3366        073956       20.00          17.00
********  End of data  ********
                                                                       Bottom
F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

Note: Because no ORDER BY clause was specified for the query, the order of the rows that is returned by the query might be different.

Only rows that contain a supplier cost that is lower than the unit cost can be seen through this view.