How to: Add Multiple Data Sources to a Query

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

You can add parent and child data sources to a query.


If you add data sources at the same level, separate SQL statements are executed sequentially.

To manage changes to AOT objects, a version control system is available. For more information, see Version Control System.

Add multiple data sources to a query

  1. In the Application Object Tree (AOT), click Queries, locate the query you want to add data sources to, and then click the query node. For information about create queries, see Accessing Data.

  2. Right-click Data Dictionary, and then click Open New Window.

  3. Drag a table, map, or view from the new window to the Data Sources node below the query node to add a parent data source.

  4. Click the parent data source, and then drag a table, map, or view from the new window to the Data Sources node below the parent data source to add a child data source.

  5. Specify how the parent and child data sources are joined by setting the JoinMode property on the child data source

  6. Create a relationship between the data sources. This information is provided later in the topic.

The following table describes the available values for the JoinMode property.




Combines records from two data sources whenever there are matching values in a common field.

For example, if you selected the records that show orders placed by each customer, the query would select only records for customers who placed orders.


Joins all the records in one data source even if there are no matching values in the joined field from the second data source.

This allows you to return records that do not have a corresponding match in the joined table. For example, data is required from all employees and their respective departments from the Employee table. As long as all employees are assigned to a department, an InnerJoin works. However, the president of the company does not have a department, and an inner join between the tables will not retrieve this data. The solution to this kind of problem is an outer join.


Combines records from one data source whenever there a value exists in a common field in another data source.

ExistsJoins are like InnerJoin, but MorphX uses only the embedded data source as a condition. Thus, the records in the embedded data source are not fetched by the query. The query will fetch the records in the primary data source only if a match is made with the embedded data source.


Selects records from one data source whenever a value in a common field in another data source does not exist. The opposite of ExistsJoin.

Create a Relationship Between the Data Sources

  • Set the Relations property to Yes on the child data source.


  • Add a relation by doing the following:

    1. Set the Relations property to No on the child data source.

    2. Right-click the Relations node, and then click New Relation.

    3. Select a field from the parent data source in the Field property.

    4. Select a field from the child data source in the RelatedField property.

    5. Save all modifications.

See also

Customizing the Query Form

Query Properties

Using Expressions in Query Ranges

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.