Bookshelf Home | Contents | Index | Search | PDF

Siebel Tools Reference > Business Objects Layer >

Joins

---

A Join object definition creates a relationship between a business component and a table other than its base table. The join allows the business component to use columns from that table. The join uses a foreign key in the business component to obtain rows on a one-to-one basis from the joined table, even though the two do not necessarily have a one-to-one relationship. Figure 92 shows the Contacts list displaying two list columns obtained from a join.

Figure 92. List Columns Obtained from a Join

A Contact business component record represents a contact person at an account. Therefore, one account record has one or more contact records, meaning that there is a one-to-many (master-detail) relationship between the tables holding account and contact information. A detail record (or row) in a master-detail relationship always has one master record (or row), as illustrated in Figure 93.

Figure 93. Master-Detail Relationship in a Join

The master-detail relationship is implemented with a foreign key column in the detail table. Multiple rows in the detail table have the same foreign key value pointing back to the same row in the master table.

Returning to the accounts and contacts example, you can look at accounts (S_ORG_EXT table rows) from the perspective of contacts (S_CONTACT table rows). Each detail table row (S_CONTACT) has exactly one master table row (S_ORG_EXT) in the one-to-many relationship. The one account row for each contact row makes it possible to treat account rows as if they were appended onto the ends of the contact rows. This provides account information about each contact's account, along with the other contact information.

NOTE:  Contact may have 1 or 0 Accounts.

A business component whose base table is a detail table in a master-detail relationship can include columns from the master table as fields. This is the principle behind a join.

Figure 94 illustrates the set of rows resulting from a join between the Contacts business component and S_ORG_EXT (accounts) table.

Figure 94. Set of Rows Resulting from a Join

In the diagram, the account number (Acct 1) in Contacts is the foreign key.

A join is always one-to-one and it is always between a business component and a table. Once a join is created, you can create additional fields in the business component based on columns in the joined table. In the diagram, the account name, city and state are fields that can be added to the Contact business component because of this join.

NOTE:  It is possible to base a join on a joined field. It is possible to use a joined field as Source Field on the join specification. This is important if, for example, you need to join in grandparent data through the parent id field on the parent business component.

Bookshelf Home | Contents | Index | Search | PDF