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.

1. 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

2. 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

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.

 

Parent topic:

Creating and using a view

 

Related tasks

Querying your database by running SQL scripts