Ova

How to join tables in SAP query?

Published in SAP Data Query 7 mins read

Joining tables in SAP Query is a fundamental process for extracting comprehensive data by combining related information from different database tables. This is achieved by defining relationships between tables, typically using common key fields, to create a unified data set for reporting and analysis.

Understanding Table Joins in SAP Query

SAP offers various tools that fall under the umbrella of "SAP Query" for creating reports and joining tables, including the QuickViewer (SQVI), SAP Query (SQ01/SQ02/SQ03), and even advanced table display transactions like SE16H. Each tool provides a distinct approach to joining tables, catering to different user requirements and technical complexities.

Key Concepts for Table Joins

Before diving into the tools, it's essential to grasp the core concepts of table joins:

  • Primary Key: A unique identifier for each record in a table.
  • Foreign Key: A field in one table that refers to the primary key in another table, establishing a relationship.
  • Join Condition: The rule that links two tables, usually by matching a primary key in one table with a foreign key in another. For example, MARA-MATNR = MAKT-MATNR.
  • Join Type:
    • Inner Join (Default): Returns only the rows that have matching values in both tables.
    • Left Outer Join: Returns all rows from the left table and the matching rows from the right table. If there's no match, NULL values appear for the right table's fields.
    • (Note: SAP Query tools primarily perform inner joins by default, though more complex tools like InfoSets (SQ02) allow for outer joins.)

Methods for Joining Tables in SAP Query

Here are the primary methods for joining tables within SAP's query environment:

1. Using QuickViewer (SQVI) for Simple Joins

The QuickViewer (SQVI) is ideal for individual users to create simple, ad-hoc queries without needing to create InfoSets. It's straightforward and user-friendly for joining up to 16 tables.

Steps to Join Tables in SQVI:

  1. Start SQVI: Enter SQVI in the SAP command field and press Enter.
  2. Create New QuickView:
    • Enter a QuickView Name (e.g., Z_MATERIAL_DATA).
    • Click Create.
  3. Define Basic Settings:
    • Enter a Title for your query.
    • Select Table join as the Data source.
    • Click the Basis Mode button (or press Enter).
  4. Add Tables:
    • Click the Insert Table button (or press F5).
    • Enter the first table (e.g., MARA for Material Master General Data).
    • Repeat to add the second table (e.g., MAKT for Material Descriptions).
  5. Define Join Conditions:
    • SAP often proposes join conditions automatically based on Data Dictionary relationships (e.g., MARA-MATNR automatically linked to MAKT-MATNR).
    • Verify/Adjust Joins: Visually check the connecting lines between tables. If a join is missing or incorrect, click the line to adjust, or click the Create Join button to manually define one. Ensure the correct key fields are linked (e.g., MARA~MATNR is linked to MAKT~MATNR).
  6. Select Fields:
    • Go back to the main SQVI screen.
    • Under List Fields, expand the tables and select the fields you want to display in your report by checking the checkboxes.
    • Under Selection Fields, select the fields you want to use as input criteria (e.g., MARA-MATNR, MAKT-SPRAS).
  7. Execute Query: Click Execute (F8) to run your query and view the results.

2. Using SAP Query (SQ01, SQ02, SQ03) for Standard Reports

For more complex, reusable, and shared reports, the full SAP Query suite (User Groups - SQ03, InfoSets - SQ02, Queries - SQ01) is used. Joins are primarily defined within the InfoSet (SQ02).

Steps to Join Tables via InfoSet (SQ02):

  1. Create/Select User Group (SQ03): First, ensure you are assigned to an appropriate User Group or create a new one (transaction SQ03). This organizes your queries.
  2. Create InfoSet (SQ02):
    • Enter SQ02 and press Enter.
    • Enter an InfoSet Name (e.g., Z_MM_MATERIALS) and click Create.
    • Provide a Description.
    • Select Direct Read of Table or Table Join as the data source. For joining multiple tables, Table Join is the most common and robust option.
    • Enter your primary table (e.g., MARA) and press Enter.
  3. Add Additional Tables and Define Joins:
    • From the menu, navigate to Insert table or click the Insert Table icon.
    • Enter the next table (e.g., MAKT).
    • SAP will often propose a join condition based on foreign key relationships. Verify this.
    • Manually Define/Modify Joins: If automatic joins are incorrect or missing, you can:
      • Drag and drop fields between tables to create a join.
      • Click on the connection lines to edit existing joins.
      • Choose the Join Type (e.g., Inner Join, Left Outer Join) if available for your InfoSet type.
      • Example Join: Connect MARA-MATNR to MAKT-MATNR.
    • Add more tables as needed (e.g., MARC for Plant Data, MARD for Storage Location Data), ensuring all necessary join conditions are defined.
  4. Define Field Groups: Group related fields together for easier selection later in the query.
  5. Generate InfoSet: Save and generate the InfoSet. This makes it available for use in queries.
  6. Create Query (SQ01):
    • Enter SQ01 and press Enter.
    • Select your User Group.
    • Enter a Query Name (e.g., Z_MATERIAL_REPORT) and click Create.
    • Select the InfoSet you just created (e.g., Z_MM_MATERIALS).
    • Follow the wizard to select fields for display (List output) and fields for selection criteria (Selection fields).
    • Save and execute your query.

3. Using SE16H for Ad-Hoc Table Joins (Advanced Display)

SE16H is a powerful transaction that allows users to perform ad-hoc queries and join tables directly for display, without creating persistent queries or InfoSets. This is particularly useful for quick data analysis and debugging. The provided reference highlights a key aspect of this:

Steps to Join Tables in SE16H:

  1. Start SE16H: Enter SE16H in the SAP command field and press Enter.
  2. Enter Primary Table: Enter the name of your primary table (e.g., MARA) and press Enter.
  3. Define Joins (Secondary Tables):
    • On the initial screen, locate the section for Join Conditions or Secondary Tables.
    • To add a secondary table and its join criteria, you'll press on the create icon (often looks like a blank sheet or a plus sign next to the 'Join Conditions' area).
    • Enter the Secondary Table Name (e.g., MAKT).
    • Specify the Join Condition. For example:
      • MARA~MATNR = MAKT~MATNR
      • MAKT~SPRAS = 'EN' (to filter for English descriptions).
    • Repeat this process for any additional tables you wish to join (e.g., MARC, MARD).
  4. Select Fields to Display:
    • Page left down to add the fields we want to use to get the data from the join. This means navigating to the section where you can select the output fields.
    • Expand the tables listed and choose the specific fields from all joined tables that you want to see in your output. You can select fields from MARA, MAKT, MARC, etc.
  5. Define Selection Criteria (Optional): Use the Selections section to filter your results (e.g., MATNR range, WERKS).
  6. Execute Query: Press Execute (F8) to display the joined data.

Example SE16H Join Flow:

Step Action Description
1 Enter MARA Set MARA as the primary table.
2 Click "Create" icon To add a secondary table.
3 Add MAKT Enter MAKT as secondary table.
4 Define Join Condition Set MARA~MATNR = MAKT~MATNR.
5 Add MARC Enter MARC as another secondary table.
6 Define Join Condition Set MARA~MATNR = MARC~MATNR.
7 Select Output Fields Choose MARA-MATNR, MAKT-MAKTX, MARC-WERKS, etc., from both primary and secondary tables.
8 Execute (F8) Run the query to see combined data.

By understanding and utilizing these various methods, users can effectively join tables in SAP to generate custom reports and extract the exact data they need for their business processes.

← Previous Article
Are Edamame Leaves Edible?
Next Article →
How do I introduce myself as a virtual assistant?