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.

Note

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

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.
Right-click Data Dictionary, and then click Open New Window.
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.
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.
Specify how the parent and child data sources are joined by setting the JoinMode property on the child data source
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.

Value	Description
InnerJoin	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.
OuterJoin	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.
ExistsJoin	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.
NoExistsJoin	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