Join Options dialog

Use this dialog to configure a join.

For more information, see:

Outer join on detail

Use this check box to create an outer join on the detail table. This returns all master rows that have no corresponding detail items, plus all matching master and detail rows.

Outer join on master

Use this check box to create an outer join on the master table. This returns all detail rows that have no corresponding master, plus all matching detail and master rows. This construct is fairly rare in real schemas. It requires the Detail foreign key can have null values option.

Detail foreign key can have null values (summary tables will not be used with this join)

Use this check box to display rows for detail items where the value may be null.

This setting should only be used on rare occasions, and only affects a particular situation when summary tables are used to satisfy a query. Foreign keys usually have values and in most cases, they are mandatory columns in the database.

This setting is required if you are setting detail rows that have no master, because it implies that the foreign key column will contain some null values. On its own it has no effect on SQL generation, but is required to determine when Discoverer can use summary tables in some special circumstances.

For example:

If you have created a summary folder that contains items from both a master and a detail folder:

and the folders are joined with this option enabled
and a user issues a query which uses items only from the detail folder,
then the summary table will not be used to satisfy the query, because Discoverer cannot guarantee that the row set is the same in both cases.

If the Detail foreign key can have null values check box is not selected, this query uses the summary table. This is this setting's only effect.

One to one join relationship between master and detail

Use this check box to create a one-to-one relationship instead of a one-to-many relationship between the master and detail tables. There is no real master and detail in this case, because there is only one row in each. These joins are unusual, but do occur in schemas occasionally.

This setting has no effect on the SQL that Discoverer generates, because SQL does not know about the cardinality of joins. It only affects the fan trap detection described in Chapter 10, "What is a fan trap?".

Because true one-to-one joins do not result in Cartesian products, it is possible to query a master with multiple detail folders, provided that all but one of the detail folders are joined with one-to-one joins. If these were all one-to-many joins, making it possible for a row in one detail table to join to many rows in another detail table, and vice versa, the result is a Cartesian product. Cartesian products are explicitly disallowed in Discoverer, because they are almost never the desired or expected result.