Learn to configure the <join_column> element for Pull Reports™ Ad Hoc
report and data service software.
Catalog Configuration Java API analog:
JoinColumn
A <join_column> specifies the columns involved in a SQL join between two
<table>s via a <relationship>.
See the <relationship>
documentation for example usage.
<relationship> |
<join_columns> |
<inverse_join_columns> |
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.
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.
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.
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.