Join data in queries

  • 6 minutes

The biggest advantage of using queries comes from their ability to join data from multiple tables into a single resulting dataset. Therefore, you would usually model queries to include multiple data items that are joined to combine the data from multiple tables into a single resulting dataset.

You can join a data item to another data item by indenting it under another data item. The data item under which you have indented another data item becomes the parent data item. Each parent data item can have only one child data item.

When you indent data items under one another, you only specify that the data from one table will be joined to the data from another table. However, you have not defined how the data will be joined. To define the data joining rules, use the following data item properties:

  • DataItemLink - Sets a reference between one or more fields of the source table of the current data item and fields of one or more source tables of the data item(s) that are higher in the data model hierarchy. You can link them by using the name of the data item.

  • SqlJoinType - Defines how the resulting dataset should handle the rows from the current data item if no match occurs between values that were specified in the data item link and data items that are higher in the data model hierarchy.

    • CrossJoin - Contains rows that combine each row from the upper data item table with each row from a lower data item table. Cross joins are also called Cartesian products. A cross join doesn't apply comparisons between columns of data items, so the DataItemLink property is left blank.

    • FullOuterJoin - Contains all records from the upper data item table and all records from the lower data item, including records that do not have a matching value for columns that are linked by the DataItemLink property.

    • InnerJoin - Creates a dataset by combining records from data item tables where a match is found between the columns that are linked by the DataItemLink property of the lower data item. Inner join uses an equal to comparison operator to match rows from the lower data item table with rows from the upper data item table that is based on the values of the linked columns.

    • LeftOuterJoin - Resembles the InnerJoin, except that the resulting dataset contains every record from the upper data item table, even if a record doesn't have a matching value in the lower data item for columns that are linked by the DataItemLink property.

    • RightOuterJoin - Resembles the InnerJoin, except that the resulting dataset contains every record from the lower data item table, even if a record does not have a matching value in the upper data item for columns that are linked by the DataItemLink property.

The following code example shows how the DataItemLink and the SqlJoinType properties are used.

query 7345 "Avail Qty. (Base) In QC Bins"
{
    Caption = 'Avail Qty. (Base) In QC Bins';

    elements
    {
        dataitem(Location; Location)
        {
            ...
            dataitem(Warehouse_Entry; "Warehouse Entry")
            {
                DataItemLink = "Location Code" = Location.Code;
                SqlJoinType = InnerJoin;
                column(Location_Code; "Location Code")
                {
                }
                ...
                dataitem(Bin_Type; "Bin Type")
                {
                    DataItemLink = Code = Warehouse_Entry."Bin Type Code";
                    SqlJoinType = InnerJoin;
                    ...
                }
            }
        }
    }
}