Sfairadora

  • Reference Guide

Joining Tables

The Join Table operation allows you to join table rows with information from another table. The operation is invoked by the Join Table command in the context menu of the table that you wish to join with another table. The command opens a window that allows you to specify which table should be joined and how. The following overview lists the parameters that must be set in the window:
Item
Description
Table to be joined
Object name
The name of the object in which the joined table is located. In most cases it is enough to leave the preset document name in place – the joined table is in the same document as the table to which it is being joined.
Data reference
Reference to a table in the object. You can select it from a drop-down list.
Relation between the base and joined table
The aim of the operation is to join appropriate rows from the table being joined to every row of the existing (base) table. In this section, you have to define the conditions that must be satisfied by the rows of the table being joined in order to be added to a particular row of the base table. A table of three columns has to be filled to define these conditions: 1. name of a column of the table being joined, 2. relation, and 3. name of the base table column. A row in the base table is joined only with those rows from the joined table, where the two specified values are in the required relation. Available relations are the same as in the table filter.
Ungroup joined table
If the field is not checked, the rows from the joined table are placed into a subtable of every row of the base table. If checked, subtables are ungrouped after the join. Should a subtable be empty, the row containing it is left out upon ungrouping.
Checking the ungroup option is useful when exactly one row from the joined table is expected to be joined with every row of the base table. When ungrouped, these two rows merge into one, which looks better than if the joined row were the sole record in a subordinate table of the base row.
Column name
If the ungroup joined table option is not selected, you can specify here the name of the column that will contain the rows from the joined table. If no name is specified, the column name is Joined.

Example 1

Orders table:
Name
Delivery
Product_code
Adam Smith
1 Feb 2007
1
Barbara Cooper
5 Mar 2007
2
Carol Taylor
20 Feb 2007
1
Daniel Parker
17 Feb 2007
3
Elizabeth Wright
2 Mar 2007
2
Frank Jones
10 Feb 2007
3
Products table:
Code
Name
Price
1
Mixer
1200
2
Kitchen scale
2380
3
Frying pan
1560
If you wish to include in the first table, in addition to the product codes, also the product details from the second table, you will need to join the tables. The relation between the columns of the joined and base table is: Code = Product_code. Since you know that exactly one row from the joined table exists for every row of the base table, check the ungroup joined table option.
The Orders table after joining with the Products table:
Name
Delivery
Product_code
Code
Name
Price
Adam Smith
1 Feb 2007
1
1
Mixer
1200
Barbara Cooper
5 Mar 2007
2
2
Kitchen scale
2380
Carol Taylor
20 Feb 2007
1
1
Mixer
1200
Daniel Parker
17 Feb 2007
3
3
Frying pan
1560
Elizabeth Wright
2 Mar 2007
2
2
Kitchen scale
2380
Frank Jones
10 Feb 2007
3
3
Frying pan
1560
In this case it is also advisable to hide the Product_code and Code columns.

Example 2

Suppose you have a Customers table containing, among others, a column named bonus. Another table named Gifts contains possible loyalty awards. This table contains, among others, a column named min_bonus representing the minimum bonus necessary to qualify for a particular gift. If you wish to display a list of possible gifts for every customer, you will need to join the Customers table with the Gifts table. The relation between the columns of the joined and base table is: min_bonus bonus. Since multiple rows (or no row at all ) from the Gifts table may be joined with a single row of the Customers table, do not check the ungroup joined table option. If the joined table contains columns that you do not need, you can remove them from the view. Naturally, you cannot remove those columns from the view that are used to define the relation between the tables (in this case bonus and min_bonus).

Example 3

Suppose you have a People table containing a column named age and you wish to have for every person a list of people that are younger than him/her. You will need to join the People table with itself again. The relation between the columns of the joined and base table is: age < age.

Note

The join table operation is equivalent to the JOIN operation in relational databases.