Join view

Join view

In order to join two or more tables, the table name should be dragged into the Retrieval component with Ctrl key down.

Another way of joining tables is to Ctrl-drag into the Retrieval component a field that belongs to a table other than the one in the header area. In this case, upon landing the field in the header, the new table is added to the table list. An even faster way of doing this is to Ctrl-click on the field without dragging it; the net effect in this case is similar to dragging, except that the field is placed in the first available slot in the Retrieval component.

The tables can be removed from the header by resizing the header box from bottom up, as shown in the figure below:

The tables are joined in the top-down order as listed in the Retrieval component's header. The Join attributes, such as the type of join, joined fields and the condition on which the fields are joined, are set in the Inspector's Join view.

When more than one table is shown in the header, each table is assigned an alias - a single letter code (shorthand) that is used to qualify field names in the join. Aliases help uniquely identify fields that come from different tables and can potentially have duplicate names.

The Join view consists of two scrollable areas: the table joins browser, and the join condition list.

The Table Joins area establishes the order of joins, which by default follows the order in which the tables are listed in the Retrieval component header. New joins are added by pressing the Add button; the Drop button removes the currently selected join.

To set a join condition for the selected join, drag the desired field tile above the text header Drag fields above this line. Once dragging is complete, the field name tile will display its four component parts:

Left table field name;
Pop-up list of available Comparison Operators (content varies, depending upon the field's data type);
Control button;
Field at which the right table is joined to the left one.

Left field name
this is the name of the field which was dragged above Drag fields above this line for inclusion in the join condition. The field deterimines the table on the left-hand side of the join, as indicated by the alias in the field name. Fields that are primary keys are shown in blue color and marked with this icon:

Comparison Operators
The list of valid comparison operators depends on the data type of joined fields. The figure below shows the operators applicable to the join of character type fields; for a more detailed treatment of the generic operators, refer to your database SQL manual dealing with the JOIN clause.

Control button and right join value
When first displayed, the Control button is set to (the joined entity is a field). This is the most frequently used option, as the joins are usually created by comparing the table fields, rather than fixed values. However, joining tables on the basis of fixed-value comparison is also possible. Setting the Control button to or will instruct the table join to use the value carried by a procedure variable or hardcoded in the text field as shown in the following figure:

Right join field
The pop-list with the right-hand table fields shows only those fields that can be used for joining on the left field. That is, before the pop-up list expands, all fields whose data type is incompatible with the left field, are removed from the list.

Grouping of the join conditions

When multiple join conditions are combined into one group, they are treated as cumulatively narrowing join - i.e. all join conditions must be met in order for the tables to be joined at the given row. In other words, they are treated on the "AND" basis. In some cases it may be desirable to group the join conditions on a different basis, using OR and parentheses. This can be done in a maner similar to grouping of the search conditions, by dragging in the grouping operators from the Palette, as in this example.

Order of the join evaluation

When more than two tables are joined in the Retrieval component header, the join clause is made of tables and their join conditions in the top-down order. The example below shows the retrieval based on 3 tables, joined at their primary key fields:

The full SQL query is shown in the Expert view, and in this example is formatted as:

select A.Quantity,B.OrderDate,C.ProductName,C.ProductID from "Order Details" A JOIN Orders B ON A.OrderID = B.OrderID JOIN Products C ON A.ProductID = C.ProductID

Automatic generation of joins

The process of setting join conditions on tables can be manual, or automated. Scribe makes this choice based on the user preferences set in the following window:

In the automatic joining mode, Scribe attempts to match the fields from left- and right-hand table, based on the following rules (here, for convenience, the table on the left side of join is called A, and the right-side table is called B):

1. If table A contains primary keys, for each primary key field Scribe tries to find a matching foreign key field in table B. The match is found if the foreign key references table A and it primary key.

2. If step 1 does not succeed, or if there are no primary keys in table A, for each field in table A Scribe tries to find a field in table B that has the same name and is data type compatible for joining on its namesake in table A.

If neither one of these steps succeeds, Scribe reverts to the manual selection mode.