Download Data from Multiple Tables with a Join

The steps below explain how to create a query with multiple tables for downloading data.

NOTE: Review the tutorial Download Data from SAP Using a Query before trying the steps below.

Creating Joins in the Query Designer

The power of queries lies in being able to act on data from more than one table. For example, to view a customer's information with the orders placed, data from the Customers and Orders tables is needed.

Data Workbench attempts to join tables based on foreign key relationships. When a table is added to the Query Workspace, joins are automatically detected and displayed.

Sometimes tables added to a query do not include any fields that can be joined. In this case, you can add one or more extra tables to serve as a bridge between the tables containing data you want to use. For example, if you add the Customers and Order Details tables to a query, there would be no join because are no fields in common. The Orders can be included in your query to provide a connection.

Occasionally you may need to join two tables but no automatic joins have been created. For these cases, Data Workbench provides a Suggest Links menu option on a table. Right click on a table and select Suggest Links from the menu. However, note that sometimes fields with the same name do not result in valid joins. .

Select the tables for which to suggest links, then click OK. Data Workbench scans the tables and offers to create links for fields with the same name in both tables.

NOTE: SAP does not allow Cartesian joins, when the tables on the query are not joined to one another, either directly or indirectly.

Creating Outer Joins

Data Workbench allows you to create outer joins on tables instead of inner joins. Outer joins are useful when all the records from a certain table are required even if they do not have any corresponding data in the joined table. SAP only allows left outer joins and thus in the Query Designer, outer joins can only be created on the table that was placed in the Query Workspace first. To create an outer join:

1. Create the join between the tables.

2. Right-click on the join in the Query Workspace. Choose the All records from ... option from the menu. The appearance of the join line changes to show a black arrow next to the 'outer' table. The outer join can also be created by right-clicking on the join, selecting Properties and then selecting the table for which to include all rows. SAP has other restrictions relating to outer joins that affect the criteria section of the Query Designer. The following restrictions apply:

  • Fields from the 'inner' table may only have one set of criteria defined in the Selection Criteria Workspace.
  • Equals (=) is the only comparison operator allowed for the criteria of these fields.
  • Follow the rules displayed in the Legend area of the Query Designer for all criteria.

Hiding Data Used to Make Joins

In cases where you need to select fields to build a query, but do not want those fields to appear in the final downloaded data, in the Selection Criteria Workspace, make sure that the Output checkbox for the field is unchecked.

Steps to Create a Query with a Join

The steps below describe how to create a query based on the MARA (Materials) table that uses data from the MARC, MVKE and MAKT tables to build a query that shows all materials originating in either Japan or Germany which are distributed using the B2 channel.

  1. On the Home page, select Download Data from SAP or go to the Data page and click Download Data from SAP.
  2. If this is your first query, click Refresh Dictionary in the Commands pane. This step ensures that you have the most recent table and data type information loaded before you create a query.
  3. The SAP GUI application typically asks for permission at several points during this process. Click Allow in the SAP GUI dialogs, or change the Security Settings in your SAP GUI configuration to avoid the dialogs.
    NOTE: It is possible that the SAP GUI pop-up is displayed behind the Data Workbench window, and therefore not visible. If the Data Workbench seems to be slow in responding, use ALT-TAB to check in there is a hidden windows waiting for input.
  4. Once the dictionary has completed refreshing, click New Query in the Commands pane.
  5. Enter a name for the new query.
  6. In the Query Designer, click the Show Short Names button at the bottom of the window.
  7. In the Tree View search field, type MARA to locate the MARA table.
  8. Double-click on the MARA table to add it to the Query Workspace.
  9. Add the MAKT, MVKE and MARC tables using the same approach.
  10. For the remainder of this tutorial, long names are used, so click the Show Long Names toggle button at the bottom of the Query Designer.
  11. Note that in the Query Workspace, the tables are linked by lines showing the common primary key Material Number.
  12. In the MARA table, click in the checkbox for the Material Number field to display it in the Selection Criteria Workspace. Note that the Output field is checked to show that the Material Number field is designated to be included in the downloaded data.
  13. In the MAKT table, click in the checkbox for the Material Description field to display it in the Selection Criteria Workspace.
  14. Add the value B2 to the Criteria field. Be sure to use upper case and press Return to complete the entry with the correct syntax.
  15. In the MVKE table, click in the checkbox for the "Country of origin of material" field to display it in the Selection Criteria Workspace.
  16. Add the value US to the Criteria field. Be sure to use upper case and press Return to complete the entry with the correct syntax.
    NOTE: Use the Legend pane on the right of the Query Designer to check that you are entering values with the correct syntax.
  17. Add the value DE to the Or field. Be sure to use upper case and press Return to complete the entry with the correct syntax.
  18. In the MARC table, click in the checkbox for the Material Description field to display it in the Selection Criteria Workspace.
  19. In the Sort Order field, optionally set a sort order to Ascending or Descending.
  20. Optionally modify the sort priority by editing the value in the Sort Precedence field.
  21. The completed query should look as follows.
  22. Click Save to save the query.
  23. Check that the query returns the correct data by clicking Preview.

    Query results are displayed in a Data Preview dialog.
  24. To perform a download, either:
    • From the Query Designer click Download.
    • or, click Save to save the query and exit the Query Designer, then click Download in the Commands pane.
  25. In the Query Limits dialog, optionally set the number of rows to download.
  26. Select a filename and location for the Excel file containing the downloaded data.
    Below is a sample Excel file showing output from the query above.
  27. Click Close to close the Query Designer and return to the Data Workbench window.
    The newly create query is displayed in the the Download Data from SAP page.
  28. If you are using tags, be sure to select All Queries in the Tag Pane to view new queries. You can assign a query to a tag by dragging and dropping the query on the tag.