<join_column>

Abstract

Learn to configure the <join_column> element for Pull Reports™ Ad Hoc report and data service software.


Table of Contents

Catalog Configuration Java API analog:  JoinColumn

A <join_column> specifies the columns involved in a SQL join between two <table>s via a <relationship>.

Usage

See the <relationship> documentation for example usage.

Parents

<relationship>
<join_columns>
<inverse_join_columns>

Attributes

Note

The names specified by the columnName and referencedColumnName attributes are database column names and not <column> ids. Furthermore, it is not required that the specified columns be included in the referenced <table> elements as <column>s.

columnName

The database name of the foreign key column. The table which contains the column depends on the nature of the relationship.

See the <relationship> documentation for relationship type definitions.

to-many, <join_column> If this <join_column> has an immediate <relationship> parent with a cardinality of many, the <relationship> is a one-to-many and the foreign key column is on the child <table> of the relationship.

to-one, <join_column> If this <join_column> has an immediate <relationship> parent with a cardinality of one, the <relationship> is a many-to-one or one-to-one and the foreign key column is on the parent <table> of the relationship.

to-many, <join_table> If this <join_column> has an immediate <join_columns> or <inverse_join_columns> parent within a <join_table>, the <relationship> is a many-to-many and the foreign key column is on the join table.

referencedColumnName

The database name of the column referenced by this foreign key. The table which contains the column depends on the nature of the relationship.

See the <relationship> documentation for relationship type definitions.

to-many, <join_column> If this <join_column> has an immediate <relationship> parent with a cardinality of many, the <relationship> is a one-to-many and the referenced column is on the parent <table> of the relationship or the table specified by referencedTablePath if present.

to-one, <join_column> If this <join_column> has an immediate <relationship> parent with a cardinality of one, the <relationship> is a many-to-one or one-to-one and the referenced column is on the child <table> of the relationship.

to-one, <join_table>, <join_columns> If this <join_column> has an immediate <join_columns> parent within a <join_table>, the <relationship> is a many-to-many and the referenced column is on the parent <table> of the relationship or the table specified by referencedTablePath if present.

to-one, <join_table>, <inverse_join_columns> If this <join_column> has an immediate <inverse_join_columns> parent within a <join_table>, the <relationship> is a many-to-many and the referenced column is on the child <table> of the relationship.

referencedTablePath (Optional)

The table resource path to the <table> or <table_ref> which contains the referencedColumnName column. The referencedTablePath attribute supports joins to a table higher in the relationship hierarchy than the immediate relationship parent table - such as a grandparent table.

There are three rules governing the referencedTablePath value.

  • Rule 1: The table resource path must reference a table equal to or higher in the path hierarchy than the parent table of the encompassing <relationship>. This requirement guarantees that the table will be present in the export SQL query. For example, if the <join_column> which specifies the referencedTablePath exists within a <relationship> of a <table> with path /foo/bar, then valid paths are /foo and /foo/bar but not /foo/baz.

  • Rule 2: Unlike other table resource paths, the base table of the report may not be excluded from the path. For example, if the base table of the report has id="foo", than the first path element must be /foo.

  • Rule 3: The referencedTablePath attribute is only permitted for to-many, <join_column> relationships or within the <join_columns> element of a to-many, <join_table> relationship.