![[spacer]](1p.gif){kind=small}
All of the member tables are related to the primary table, either directly or indirectly. The member tables that are directly related to the primary table are located on a main branch, and their main table is the primary table. Indirectly related tables are those that are linked to the primary table through the related member tables. Indirectly related tables are located on subbranches. Their main tables are the member tables that they have been joined to.
While the key for the primary table provides access to the virtual table, the keys of the non-primary member tables join the tables together. To join the table columns, a field or fields from the primary table are related to the key in the secondary table. To relate fields and keys, the storage type and the storage size must be identical. At runtime, the relationships provide access to data in all member tables. The database will pass the record (row) formed from the relationships stored in the virtual table to the user when all of the data has been collected.
Member tables on main and subbranches can be related through one-to-one and one-to-many relationships. There are rules that govern the combinations of one-to-many relationships that exist. When member tables have been related so that more than one main branch exists, only one main branch can contain one-to-many relationships. If the relationship that you are trying to create is illegal, the key will not appear in the Relationship Key field in the Relationship Definition window.
Along with one-to-one and one-to-many relationships, joins also determine how relationships are created. Joins regulate how data is gathered and how the columns of data are related. Virtual tables allow two types of joins: equal and left outer.
Equal joins require that only complete records are included in the virtual table’s result set. If any member table doesn’t contain a corresponding record, the entire record won’t be included in the virtual table.
Left outer joins do not require all fields to be complete when added to the virtual table. A left outer join will add all of the fields specified by the primary table’s key, whether data exists in the secondary table or not. When a secondary table does not contain data in the related fields, a null value will be inserted instead, based on the field’s storage type. If a record does not exist in the primary table, no data from the secondary table will be returned.
For example, the following report was generated from a virtual table that uses a left outer join. The dash signifies that there wasn’t corresponding data in the joined secondary table.
When using either type of join, the join column is not required to be a member column of the virtual table.