A view is a collection of columns from the available tables. Although you can think of a view as a temporary table, a view does not store data. A view is a stored query. The query specifies the tables, columns, and linking columns that make up the view. Each time you use the view, R:BASE reconstructs it from the query.
You've learned how to look at data from individual tables in the SkyWrite database and how to find the information you want in those tables. But what if the answer to a question requires information from more than one table in SkyWrite? To answer a question like "Which salesperson booked flights for the Cascade Limousine company?" you'll need to query multiple tables. By learning to use multi-table queries and views, Amelia, Charlie, or anyone in SkyWrite Ink can answer specific questions quickly.
While designing and building the database in Lessons One and Two, we stressed that tables should share common columns. Each of the three tables we created has a separate theme: Employees, Customers, and the Flights made for customers. Each table is related to another table by at least one common column: the EmployeeID column or the CustomerID column. Now you will see the benefits of these linking columns.
Suppose you wanted to see a list of customers and all their corresponding flights like the one below:
Last Name Company Slogan Total Charge
Beck Cascade Limousine LIMOS 555-9987 $700.00
Earle Domsea Horse Farms HORSE 'n AROUND $650.00
Rubin Regency Bottling Co. R POP HITS THE SPOT $675.00
The Flights table contains all the flight information but shows only the customer number, not the customer's name or company. That information is contained in the Customer table.
We can gather the information we want from each table by creating a query that combines the two tables. Once the query is defined, we can save it as a view. Then we can use the view as much as we would use a table in the database.
The illustration below shows how the rows and columns in the Flights and Customer tables combine to make FlightView, the view we'll create in this lesson.
The two tables combine based on their common column, CustomerID. When you link the two tables in the query, R:BASE compares each row in the Customer table with each row in the Flights table. When rows in the CustomerID column match, R:BASE creates a row in the view that contains the columns from both tables. For example, when CustomerID contains a "1" in both tables, R:BASE adds a row to the view. Because the relationship between the Customer and Flights tables is one-to-many, the view will contain multiple rows for customers who have more than one flight.
To create a view, first build a query that defines the view. Creating a multi-table query requires three steps:
•Choosing the tables containing the information you want to see
•Linking the tables through common columns
•Choosing the columns containing the information you want to see
Once you have created the query, you can save it as a view. You build the query and save the view in the "Query Builder" window. Remember, a view is just a stored query.
