Chapter 3 Building Physical Diagrams

Defining view reference joins

A join is a link between a column in a parent table or view and a column in a child table or view that is defined within a view reference.

If you create a new view from existing views, the joins defined on these views influence the WHERE statement in the SQL query of the new view.

Example

French_Store is a view of table Store. You define a join between Store_ID in the table and STORE_STORE_ID in the view.

Customer_Orders is a view of table Orders. You define a join between Order_No in the table and ORDER_ORDER_N in the view.

You create a view reference between French_Store and Customer_Order in which you define a join between ORDER_ORDER_STORE and STORE_STORE_ID. This is to establish a correspondence between the store ID and the store where the order is sent.

If you create a view from French_Store and Customer_Orders, you can check in the SQL query page of the view that the SELECT order takes into account the join defined between the views. The SELECT statement will retrieve orders sent to French stores only.

In the Joins page of a view reference property sheet, you can use the Reuse Columns tool to reuse existing child columns with same code as parent columns.

To define joins in a view reference:

1. Double-click a view reference in the diagram to display the view reference property sheet.
2. Click the Joins tab to display the Joins page.
3. Click the Reuse Columns tool to reuse existing child columns with same code as parent columns.
   
   or
   
   Click the Add a Row tool.
   
   A join is created but you have to define the parent and child columns.
4. Click in the Parent Column column and select a column in the dropdown listbox.
5. Click in the Child Column column and select a column in the dropdown listbox.
   
6. Click OK.

Copyright (C) 2005. Sybase Inc. All rights reserved.