Edit Join dialog: Options tab

Use this dialog to specify whether detail items exist in the master folder and optionally specify an outer join and the join relationship (also known as the cardinality of the join).

This dialog is also displayed as:

Join Wizard: Step 2 dialog

For more information, see:

"What are joins?"

"What are single item joins?"

"What effect do joins have on query results and query performance?"

"How to create joins"

Choose the join details

Use this option to specify whether Discoverer can assume that detail item values exist in the master folder for this join. For more information about which option to select, see "Examples of how joins can affect query results from complex folders".

• Detail item values always exist in the master folder (Typical)

  Use this option to display rows for detail items assuming that values in the detail folder always have corresponding values in the master folder (for more information, see "What effect do joins have on query results and query performance?")

• Detail item values might not exist in the master folder

  Use this option to not display rows for detail items assuming that values in the detail folder do not always have corresponding values in the master folder (for more information, see "What effect do joins have on query results and query performance?").

For more information about when to use these settings, see "Examples of how joins can affect query results from complex folders".

No outer joins

Use this option to not create any outer joins, and return all matching master and detail rows.

Outer join on detail

Use this option to create an outer join on the detail table. This returns all master rows that have no corresponding detail items, and all matching master and detail rows. For more information, see "What are outer joins?".

Outer join on master

Use this option to create an outer join on the master table. This returns all detail rows that have no corresponding master items, and all matching detail and master rows. This construct is rare in real business scenarios. To use this construct you must select the Detail item values might not exist in the master folder option. For more information, see "What are outer joins?".

One to one join relationship between master and detail

Select 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 each row in the master table can correspond to no more than one row in the detail table.

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 (for more information, see "What are fan traps, and how does Discoverer handle them?").

For more information about join relationships, see "What are one-to-many joins, one-to-one joins, and many-to-many joins?".