Types of Joins

In this section:

The following section describes the type of joins you can create using the Join tool.


Top of page

x
Dynamic Joins

Dynamic joins allow you to join two or more related data sources and create a virtual structure for reporting. The data sources remain physically separate, but Developer Studio treats them as a single structure. Dynamic joins include:

You can use dynamic joins for the duration of your session or until you clear the Join. Dynamic joins can read data from the joined data sources using the TABLE and GRAPH facilities.


Top of page

x
Conditional Joins

Conditional joins use WHERE-based syntax to specify joins based on conditional criteria, not just on equality between fields. Additionally, the host and cross-referenced join fields do not have to contain matching formats.

Suppose you have a data source that lists employees by their ID number (the host file) and another data source that lists training courses and the employees who attended those courses (the cross-referenced file). Using a conditional join, you can join employee ID in the host file to employee ID in the cross-referenced file to determine which employees took training courses in a given date range (the WHERE condition). For information on how to create conditional joins, see Using Conditional Joins.


Top of page

x
Single Instance and Multiple Instance Joins

Reference:

The Join tool allows you to create single instance and multiple instance joins. A single instance, or one-to-one join structure matches one value in the host data source to one value in the cross-referenced data source. Joining an employee ID in an employee data source to an employee ID in a salary data source is an example of a single instance join.

A multiple instance or one-to-many join structure matches one value in the host data source to multiple values in the cross-referenced field. Joining employee ID in the employee data source of a company to employee ID in a data source that lists all the training classes offered by that company would result in a listing of all courses taken by each employee, or a joining of the one instance of each ID in the host file to the multiple instances of that ID in the cross-referenced file.

When you create a dynamic or conditional join using the Join tool, you can specify whether the join is a single instance or a multiple instance.



x
Reference: Descriptions of Single Instance and Multiple Instance Joins

Type

Description

Example

Single instance (one-to-one)

Each host record has at most, one matching record in the cross-referenced file.

If you join EMPLOYEE to JOBFILE, each employee can have only one job code.

Multiple instance (one-to-many)

Each host record can have many matching records in the cross-referenced file.

If you join JOBFILE to EMPLOYEE, one job code can match many employee records.

Note: See Data Source Requirements for Cross-Referenced Fields for a discussion on special conditions that apply to the cross-referenced file in a Join.



Example: Single Instance and Multiple Instance Joins

The following diagrams illustrate single instance and multiple instance Joins.

single and multiple instance joins


Top of page

x
Joins Based on Virtual Fields

Joins that are based on virtual fields use DEFINE-based syntax to create a virtual field in the host file that you can then join to a real cross-referenced field. The DEFINE expression that creates the virtual host field may contain only fields in the host file and constants. It may not contain fields in the cross-referenced file.

You can join a virtual field to more than one real field in more than one cross-referenced file using separate Join commands, each referring to the same host file. For information on how to create Joins based on virtual fields, see Using a Virtual Field as the Join Field.


Top of page

x
Left Outer Joins

When a report omits host rows that lack corresponding cross-referenced rows, the join is called an inner join. When a report displays all matching rows plus all rows from the host file that lack corresponding cross-referenced rows, the join is called a left outer join.

See How to Create a Left Outer Join With the Join Tool in Developer Studio. For details on left outer join syntax, see the Creating Reports With WebFOCUS Language manual.

Note: If the SET ALL=ON or SET ALL=PASS setting is applied, it impacts all joins in the procedure and they are treated as left outer joins.


Top of page

x
Host Records With No Matching Cross-Referenced Records

Sometimes there is no matching record in the cross-referenced file. A host record that has no matching cross-referenced record is called a short path.

The report that results when a host record lacks a corresponding cross-referenced record depends on the following factors:


WebFOCUS