Creating a Multitable View

Article
10/23/2006

To access related information that is stored in separate tables, you can create a view and add two or more tables, or you can modify an existing view by adding tables. To add the tables, you can use the View Designer or the CREATE SQL VIEW command. After adding the tables, you can expand your control of the view results using the join condition you define between the tables.

To create a multitable view

In the Project Manager, create a view and add the tables you want in the View Designer.

-or-
Open a database and use the CREATE SQL VIEW command, adding table names to the FROM clause and join conditions.

Just adding the tables to the CREATE SQL VIEW command produces a cross-product. You need to specify a join condition in either the FROM clause or the WHERE clause of the statement to match related records between the tables. If persistent relationships between the tables exist, they are automatically used as join conditions.

Creating a Multitable Remote View

When you connect to a remote data source, you can gain access to many related tables. You can select the tables you need and adjust the relationships between them, if necessary, to get the information you need.

To create a multitable remote view

From the File menu, choose New, select Remote View, and choose New File.
In the Select Connection or Data Source dialog box, select a predefined connection or an available data source.
If required, log on to the server.
In the Open dialog box, select the tables you want to use.
In the Join Condition dialog box, accept the default join, or set up a join as needed.

As with local views, you can use the options in the Update Criteria tab in the View Designer to control how updates are made to the source tables.

Defining and Modifying Join Conditions

Typically, to define a join condition, you use the relationships established on the primary and foreign key fields between the tables. For example, you might want to find information on the orders, including information on the customer who placed the order. You can create a view using the Customer and Orders tables. You specify a join condition to compare values in the fields they have in common and, usually, return those that are equal. In the example, Customer and Orders both have a Customer ID field.

To define join conditions in a view

In the Project Manager, create or modify a view, and then add the tables you want in the View Designer.

-or-
Open a database and use the CREATE SQL VIEW command, adding table names to the FROM clause and join conditions to the FROM clause.

The following code creates the new view as described in the example above, using the FROM clause to specify the join conditions for the view:

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ;
   SELECT * FROM testdata!customer ;
      INNER JOIN testdata!orders ;
      ON customer.cust_id = orders.cust_id

The join condition has several aspects: the type of join, the fields to join on, and the operator for comparing the fields. In this case, which is an inner join, only rows from the customer table that match one or more records in the orders table are included in the result.

To change the results of the view to meet your specific needs, you can specify:

Fields in the join
Comparison operators between the fields
A sequence of joins, if you have two tables in your view
The type of join

Specifying joins on fields other than the primary and foreign keys can be useful in specific instances, but are not used in most views.

By changing the comparison operator, you can control which records are compared and returned in a manner similar to a filter. For example, if you are using a date field in the join, you can use the comparison operator to include only records before or after a certain date.

For more information about the sequence of joins, see Defining Multiple Join Conditions.

Choosing a different join type allows you to expand your query results to include both records that match the join condition and those that do not. If you have more than two tables in your view, you can change your results by changing the order of joins in the FROM clause.

You can modify the join types in your view using the View Designer or the language.

To modify a join type

Select the Join tab.

-or-
Double-click the join line.

-or-
Open a database and use the CREATE SQL VIEW command, adding table names and join conditions to the FROM clause.

Including Non-Matching Records in Results

If you want to include non-matching rows in your results, you can use an outer join. For example, you might want a list of all customers and whether or not they have placed an order. In addition, for customers that have placed orders, you might want the order numbers included in the view. When you use an outer join, the empty fields of the non-matching rows return null values.

You can also use the language to create this view by using the following code:

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ;
   SELECT * FROM testdata!customer ;
      LEFT OUTER JOIN testdata!orders ;
      ON customer.cust_id = orders.cust_id

To control which non-matching records are included in your view, you can choose from the following join types.

To	Use
Return only records from both tables that match the comparison condition set between the two fields in the join condition.	Inner join
Return all records from the table to the left of the JOIN keyword and only matching records from the table to the right of the keyword.	Left outer join
Return all records from the table to the right of the JOIN keyword and only matching records from the table to the left of the keyword.	Right outer join
Return matching and non-matching records from both tables.	Full outer join

Defining Multiple Join Conditions

If you create views or queries with more than two tables, you can change the results by the order your join conditions are specified. For example, you might want to find information on the orders, including information on the employee who made the sale and the customer who placed the order. You can create a view using the customer, orders, and employee tables and specify inner join conditions on the fields they have in common: customer and orders both have a customer ID field; orders and employee both have an employee ID field.

This view has the following underlying SQL statement:

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ;
   SELECT * FROM testdata!customer ;
      INNER JOIN testdata!orders ;
      ON customer.cust_id = orders.cust_id ;
      INNER JOIN testdata!employee ;
      ON orders.emp_id = employee.emp_id

Using Joins in the WHERE Clause

You can specify your join conditions in the WHERE clause; however, you cannot specify a join type as you can in joins in the FROM clause. For remote views, the join clause always appears in the WHERE clause.

The following code creates the same view as the previous example, using the WHERE clause to specify the join conditions for the view:

OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_emp_view AS ;
   SELECT * FROM testdata!customer, ;
      testdata!orders, testdata!employee ;
   WHERE customer.cust_id = orders.cust_id ; 
   AND orders.emp_id = employee.emp_id