Establishing relations and joins

When you query an SQL database, you are able to submit highly complex SQL statements for defining the query. Joins are expressions that combine data from several related tables in the data source by matching key-field values. The result of the query is highly dependent on the way that you construct the join expression. For example, you might define a join that will combine order header information from one table with all of the matching order line records from another table and then return the result as a single record (with a subset) in response to a single query in which only an order number was specified.

When you establish join criteria, you essentially create new "virtual" tables based on the content of actual tables from the database. These will appear as regular tables when you select fields to map to a business object attribute. It is perfectly normal to wind up looking at what looks like the same field drawn from two different tables; in this case, one or both of the tables would actually be a join. The resulting value would then depend on which join sired the field that you select. For more information about configuring attributes, see Mapping business object attributes to database fields.

Often, you will use joins to establish values for business object subsets, as in the order form example mentioned above. However, you can also use them to provide simple translations, such as mapping a country ID to a local-language country name.

To configure the mappings between a business object and the various related tables from the database, you must define the following:

• Table relations – These name two database tables and specify which fields from these two tables must be compared to find related records. The specified fields are the key and foreign key of the two tables respectively. Some joins will specify multiple table relations; this is to allow for compound keys where more than one field must be considered to identify unique records in each table.

• Join criteria – These enable related tables to be combined to establish attribute values and subsets. They are established using the Table Join Hierarchy window, as described below.

This section assumes that you are familiar with how table relations and joins are used in SQL statements. If you need more information about these concepts, please refer to an SQL reference.

Before you can begin establishing subset mappings for a business object, you must first establish the primary table mapping for that object as described in Primary business object mappings . Joins and relations do not apply to stored procedure mappings.