Configuring Siebel Business Applications > About Business Components, Fields, Joins, and Links > About Joins >

How Siebel CRM Constructs a Join

Figure 17 illustrates how Siebel CRM constructs a join.

Figure 17. How Siebel CRM Constructs a Join

Siebel CRM uses the following objects to construct a join:

1. Business Component. The business component is the parent of the join. Because of the join, a field in the business component that is joined can represent a column from the joined table.
2. Joined field. A joined field is a field in a business component that represents a column from a table other than the base table of the business component. For more information, see How Siebel CRM Uses a Joined Field.
3. Join. A join is a child of the business component. The join uniquely identifies a join relationship for the parent business component and provides the name of the joined table. The Table property of the join identifies the joined table. The join includes a child join constraint, which is an object type that contains a constant value search specification that Siebel CRM applies to a column during a join. Siebel CRM uses it with an outer join. For more information, see Options to Filter Data Displayed in an Applet.

   NOTE:  If Siebel CRM must retrieve all records in the business component even if the joined fields are empty, then you must make sure the Outer Join Flag contains a check mark.

4. Join Specification. The join specification is a child of the join. It identifies the foreign key field in the business component and the primary key column in the joined table. For more information, see How Siebel CRM Uses the Join Specification.
5. Foreign key field and foreign key column. The Source Field property of the join specification identifies the foreign key field. It represents a foreign key column in the base table, and it references rows in a specific table that Siebel CRM uses in a join. For example, in the Contact business component, the foreign key field to the join on accounts data is the Account Id field, which represents the PR_DEPT_OU_ID column in the base table.
6. Joined table. The joined table is the parent table in the parent-child relationship. It provides columns to the business component through the join. The Table property of the join identifies the joined table.
7. Primary key column. The Destination Column property of the join specification identifies the primary key column in the joined table. Tables in Siebel CRM include a ROW_ID column that uniquely identifies rows in the table. ROW_ID is the destination in most joins.
8. Mapped column. Columns in the joined table are available for use in fields in the business component.

How Siebel CRM Uses a Joined Field

A joined field obtains values through a join. The name of the join is included in the Join property of the field. The Join property and Column property together identify the column and how to access it. If you create a joined field in a business component, then you can change the Type property from the default DTYPE_TEXT to a more appropriate type. For example, if you join a table column that contains phone numbers, then you can change the Type field to DTYPE_PHONE.

How Siebel CRM Uses the Join Specification

The Source Field property of the join specification identifies the foreign key field in the business component. If left empty, then the Source Field is the Id field, which indicates a one-to-one relationship between the business component and the joined table. Siebel CRM sometimes defines a system field as the foreign key field in the Source Field property. The Created By and Updated By fields are examples of system fields. For more information, see System Fields of a Business Component.

The Destination Column property identifies the primary key column in the joined table. If the join occurs on a column other than ROW_ID, then the Destination Column property must not be empty. An empty value in the Destination Column property indicates that the destination column is ROW_ID, which is typically the primary key in a table.

In rare situations, multiple join specifications can exist in a single join. For example, the Sub Campaign business component includes a join to the S_LANG table with two join specifications. In these situations, the source fields in the join specifications must reference the same table. For more information, see Join Specification.

How Siebel CRM Filters Duplicate Records from a Join in an Applet

A join between two business components can return one or more records. For example, if the joined table is an intersection table. However, in the applet, Siebel CRM displays only the first record in the result set. An applet that references a business component cannot display duplicate records from the base table of the business component.

For example, in Siebel CRM, there is a many-to-many relationship between the Service Request and Organization business components. The link between these business components is Service Request/Organization, and the link uses the S_SRV_REQ_BU table as the intersection table. In the Service Request business component, you can add a join to the S_SRV_REQ_BU table and a related joined field. If you query the business component to retrieve a service request, then the SELECT statement retrieves all the organizations that are associated with the service request. However, Siebel CRM displays only one service request record in the Siebel client. To view all the organizations that are associated with the service request, the user can open the multi-value group applet that references the Organization business component.

For more information, see Guidelines for Naming an Object.