Defining Table Joins with the Help of Graphics

Prerequisites

On the InfoSet: Title and Database or Create QuickView: Select Data Source screen you selected the data source Table Join ( Using Table).

If the corresponding software requirements have been met, the graphical join definition is the standard setting.

Context

If several tables are all linked within a SELECT statement, this is known as a join. The result set is a table, the lines of which contain all the fields of all the tables used in the join.

The links between the various tables used in the join are all specified separately. You use these conditions to specify which combinations of records from the individual tables are included in the result set.

Table joins must be defined already, before you maintain InfoSets and QuickViews.

Procedure


  1. You are in the Join Definition screen. You can choose from the following functions for defining table joins:

    Inserting a Table

    Add table In the Add Table dialog box, specify the name of the table that you want to add. Choose Continue.

    The table is displayed in a window: The table fields are displayed along with their corresponding long texts. Key fields are marked with a symbol. You can move the position of the window however you like.

    Propose join conditions

    The system proposes the standard default table joins. The proposals are determined from the foreign key dependencies stored in the dictionary, or from the key fields in the tables that are used in the join.

    The join conditions between the individual tables in the join are displayed as lines.

    Create join condition

    Select the field where the join condition starts. Use the Drag&Relate function to join this field with its target field. If the join condition is valid, the join is displayed as a line.

    Define the type of join condition

    You can choose from the following functions when you open the context menu for the join-line:

    • Display Join Condition You get to the Join Condition dialog box. This is where you define the type of join:

      • Internal

        If according to the join conditions of a record in the first table, there is a corresponding record in the second table, this second record is included in the result set also.

      • Left-outer

        All of the records in the first table are included in the result set.

        If, in the second table, there is no record corresponding to a record in the first table, a record containing fields with initial values (blank fields) is used in the second table.

    • Delete join condition

    Delete table

    You get to the Delete Table dialog box. Specify the name of the table that you want to delete. Choose Continue.

    Alias Tables

    The dialog window InfoSet: Alias Names for Tables appears. You use this function if you want to include the same table twice in a join. Choose Create. Specify an alias for the table. You can include the table in your join under both its original name and its alias.

    For more information, see Assigning Additional Tables.

    Check

    The system checks the validity of the join conditions between the tables you have selected, and looks for any tables that have been added but not joined.

    Reference Field

    When you place your cursor on an quantity field or a currency field and choose the Reference field function, the system displays the reference table that contains the corresponding unit of measure or currency for this field.

    Field Documentation

    Select a field from a table. Click on the Field documentation function to display information on the technical definition of a field.

    Two fields sharing the same domain can always be joined.

    Two fields sharing the same data type (including length attributes) in the data dictionary, can also be joined.

    For more information on restrictions and rules governing the use of joins, see Defining Table Joins Without the Help of Graphics.

  2. When you have finished defining a table join, save your work according to the following:

    • Table joins for a QuickView:

      In the Join Definition screen, choose Back.

      The join definition is saved together with your QuickView.

    • Table joins for an InfoSet:

      In the Join Definition screen, choose Back.

      Save your entries on the following screen.

  3. You can change the table join and the join conditions at any time. Choose Join. This takes you back to the Join Definition screen.

    With a table join as a data source for an InfoSet, you can:

    • Delete each table from a table join, provided that no fields from the deleted table are assigned to field groups.

    • Move each table to a different position in the join, so that a different table becomes the first table in the join.

    • Delete all but one of the tables from a table join.

    • Add an extra table to define a table join (with InfoSets that use a single table).

Results

The result of a table join is again a (flat) table. You cannot, therefore, use a table join to analyze hierarchical relationships between tables. We recommend you use logical databases in this case.

For more information see the online documentation on the SELECT ABAP statement.