Show TOC

Procedure documentationDefining Table Joins with the Help of Graphics Locate this document in the navigation structure

 

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.

Prerequisites

In either the InfoSet: Title and Database screen or the Create QuickView: Choose Data Source screen, you have chosen the Table Join (Using Table) data source.

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

Note Note

From the InfoSet: Initial Screen, follow the menu path   Settings   Settings  . Put a checkmark in the Graphical Join Definition checkbox accordingly.

For more information, see Defining Table Joins without the Help of Graphics.

End of the note.

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.

        Note Note

        We recommend that you do not add additional tables to tables that have been joined using a left-outer join.

        End of the note.

    • Delete join condition

    Deleting Tables

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

    Note Note

    Clicking on the pushbutton that closes the window in the title bar of the table window is another way of removing a table.

    End of the note.

    Alias Tables

    This takes you to the InfoSet: Alias Names for Tables dialog box. 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).

Result

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.