Organizing Search Results and Using Indexes - Java Database Programming Bible - page 266

Figure 8-5 shows how using the view to the change Fredo Corleone's street address propagates
through to the Customers Table.

Figure 8-5: Updating a view updates the underlying table.

Recall that a view is really nothing more than a named result set made accessible as if it were a table.
Creating a view from a complicated query is just as easy as creating one from a simple query.

One way to retrieve data from multiple tables is to use an INNER JOIN . The next example shows how
to use an INNER JOIN to retrieve data from four different tables, creating a view called
"Orders_by_Name":

CREATE VIEW Orders_by_Name AS

SELECT c.LAST_NAME + ', ' + c.FIRST_NAME AS Name,

COUNT(i.Item_Number) AS Items, SUM(oi.Qty * i.Cost)

AS Total

FROM ORDERS o INNER JOIN

ORDERED_ITEMS oi ON

o.Order_Number = oi.Order_Number INNER JOIN

INVENTORY i ON

oi.Item_Number = i.Item_Number INNER JOIN

CUSTOMERS c ON

o.Customer_Number = c.CUSTOMER_NUMBER

GROUP BY c.LAST_NAME + ', ' + c.FIRST_NAME

Cross-Reference

JOINS are discussed in Chapter 9 .

You can now query this view in the normal way to get a summary of customer orders by name as
shown in the following table.

Name Items Total

Adams, Kay 3 6.96