The Joins Tab

Related Topics

Joins create associations between tables so that their rows may be presented together in the same report. Multiple joins associate columns across many tables to create powerful data visualizations when used in reports. The number of tables and joins in the Domain depends on your business needs. The server supports the four most common join types, all based on equality between values in each column.

Use the Joins tab to join tables for the Domain. On the Joins tab, the list of selected and derived tables is duplicated in the Left Table and Right Table panels. Expand tables in both panels, select a column in each table having the same logical meaning and compatible formats, and then click a join icon:

Icon	Name	Description
	Join Inner	The result contains only rows where the values in the chosen columns are equal.
	Join Left Outer	The result contains all the rows of the Left Table, paired with a row of the Right Table when the values in the chosen columns are equal or contain blanks. If the support cases are in the Left Table of the example, the result of a left outer join contains all support cases even if they do not have an assigned engineer.
	Join Right Outer	The result contains all the rows of the Right Table, paired with a row of the Left Table when the values in the chosen columns are equal or contain blanks. If the users are in the Right Table of the example, the result of a right outer join contains all the users and the support cases assigned to each engineer, if there are any. In this example, a user might also appear several times if different support cases refer to the same support engineer user ID.
	Full Outer Join	The result contains all rows from both tables, paired when the joined columns are equal, and filled with blanks otherwise.

The new join appears in the All Joins | Joins on Selected Table panel.

In order to create a join between two tables, each table must have a column with the same meaning. For example, a table with data for support cases has a column for the assigned engineer user ID that can be joined with the table of user data that has a user ID column.

In some cases, you may need to duplicate a table in order to join it several times without creating a circular join, or in order to join it to itself. You can also duplicate a table so it may be joined with different tables for different uses. Click the following buttons above the Right Table panel to make a copy, change the name, or delete a table:

Copy: Copies the selected table and gives it a name with sequential numbering. The copy appears in both the Left Table and the Right Table.
Change ID: Changes the name of the selected table. The new name becomes the ID of the table throughout the Domain, and is updated everywhere it appears in the Domain Designer.
Delete: Removes the table from both lists. If the deleted table was the only instance of a table, removing it on the Joins tab also removes it from the list of selected tables on the Tables tab.

You use the All Joins | Joins on Selected Table panel to see the defined joins, to remove a join, and to change the join type. Select between the All Joins and Joins on Selected Table displays based on the following criteria:

All Joins: Lists all joins defined for the Domain.
Joins on Selected Table: Lists only joins defined on the table you select, simplifying the view you have of many joins.

Once a join has been created and displayed on the All Joins | Joins on Selected Table panel, you can change the join type from the Join Type list, or click the Remove button to remove the join from the panel and the Domain design.

After creating joins, one or more join trees appear on the Calculated, Pre-Filters, and Display tabs. For example, if you join tables "A" and "B," "B" and "C," then join tables "D" and "E," the result is two join trees. Columns of table "A" and table "C" may appear in the same report because their tables belong to the same join tree. Tables "A" and "D" are said to be unjoined—their columns may not be compared or appear in the same report. Tables that are not joined appear individually along with the join trees.