Joins
When multiple tables are used to create a report, each table must be linked to all other tables being used. Linkages tell the system how the data in one table relates to the data in the other table(s). These linkages are referred to as "joins."
Join relationships between tables are typically made between two related elements (e.g., Vendor ID in Table A to Vendor ID in Table B). One must create as many join relationships between two tables as is necessary to guarantee reporting of distinct data records.
Inner Join
An inner join links two or more tables and retrieves information from all tables that contain the same link value. Inner joins direct the system to retrieve data from all tables that have matching data. For example, if you want to retrieve names and addresses of vendors only when both a name and an address have been entered into the database, an inner join would be used. Any vendors for whom an address is not found would not be included in the report.
Outer Join
An outer join is more inclusive. Outer joins link multiple tables and direct the system to retrieve data regardless of whether exact matches in data are found. In this way, an outer join between a Vendor Name table and a Vendor Address table would direct the system to return all vendors, regardless of whether an address is present or not.
Creating a Join
To create a join, simply select the data element from one table and drag it onto the matching element on the second table. CDD will draw a line from one table to the other, indicating that the join has been created.
By default, CDD assumes that all newly created joins are inner joins. Inner joins are easily recognizable by the type of line that CDD draws between the two tables: both ends of the line are marked by a square.
Outer joins are recognizable by the arrow that CDD draws between the two tables. The arrow indicates the relationship of the join; the base of the arrow indicates the table the join is drawn from, and the head of the arrow indicates the table the join is drawn to.
Editing Joins
To edit joins, while in the Category window, select Edit > Edit Joins from the main menu. The Join Properties window is displayed. It consists of multiple tabs, including a General Information tab and a tab for each of the joined tables.
The General Information tab controls how the elements in the tables are identified. The Join Elements area allows you to select among Catalog Names, Database Names, Item Names, and Alias Names when viewing the data elements in the category window.
The tabs representing the joined tables allow you to edit the joins themselves. You can change the join from inner to outer, or from outer to inner, using the radio buttons at the bottom of the screen.
Once you have edited all joins and are satisfied with their configuration, click Close to return to the Category window.
Deleting a Join
To delete a join, while in the Category window:
- Select Edit > Edit Joins from the main menu.
- In the Join Properties window, select the join you want to remove from the join details area of the tab and click Remove. The join is removed from the category.
Note that you will not be prompted for confirmation when removing joins. Since joins are easily created, if you mistakenly remove a join, you can return to the Category window to recreate the join.