Joining tables in a query
You have two ways to create joins between tables in a query, automatically or manually. Any joins defined between two tables are represented as arrows connecting the key fields from the two tables.
Joining tables automatically
The Auto Join option on the Query menu enables you to join tables together automatically based upon the default criteria. You can choose to join tables in a query by any of these criteria. However, if you have specified to use pre-joins in your queries, the Auto Join feature will not take effect.
- Foreign Keys
If there is a column that is defined as foreign key in one table and a primary key in another at the same time, these two tables will be joined together.
- Primary Keys with Same Names
If a column is defined as a primary key in one table and appears in another at the same time, these two tables will be joined together.
- Same Column Names
Tables with the same column name will be joined together.
Joining tables manually
To create manual joins, take one of the following two ways:
- Position the mouse pointer over the column that will be the source of the join, then click and hold the left mouse button while dragging the join away from the source column to the destination column.
- Select the columns from two tables while pressing CTRL, and then click Query > Join Columns.
The join relationship is then established between the tables. When more than one relationship is required between two tables, you can create multiple joins between them.
Editing a join
No matter whether a join is created automatically or manually, you can further edit it if required. To do this:
- Double-click the join button
in the join line. The Join Options dialog appears. See the dialog.
- Check the Outer Join option to set the join an outer join.
- Click either the Left, Right or Full radio buttons. Regardless of where the tables are placed in the Query Editor, Left is where the arrow starts and Right is where the arrow points.
- Edit the join conditions in the Condition panel according to your requirements.
- When done, click OK to accept the changes.
Notes:
- The feature of multiple joins (more than one join existing between two tables) is supported.
- In JReport Designer, the joins in one path should never form a loop (any table in this path will have direct or indirect joins with all the other tables). If you specify a path which forms a loop, JReport Designer will prompt you to re-select the joins.
- Not all forms of joins are supported by all database systems; for example, MySQL does not support Full Outer Join so be sure to check your DBMS manuals.
Alerting when Cartesian product is used
A Cartesian product is used when tables link together with no specifications.
For example, Table A has three values: A, B and C. Table B has three values: 1, 2 and 3. Value A matches value 1, value B matches value 2, and so on. This is a specific match. However, a Cartesian product could have value A matching with 1,2 and 3, and value B matching with 1, 2 and 3, and so on. Depending on the data values, Cartesian products can produce a large and complicated dataset as unnecessary information may be contained.
You can specify whether to alert when this happens as follows:
- On the Query menu of the Query Editor, click Current Query Options.
- In the Query Option dialog, check or clear Warning When Cartesian Exists.
