The Join or Data Blending query of DataEngine allows you to combine or blend data from several tables (base tables or query result tables) into a single table. It is a special kind of query that does not have any calculation or aggregation operations.

Note that the Join query solely serves the purpose of joining tables. If you need calculations and/or aggregation subtotals, you can do this by feeding the results of the join query as input to another query.

All the queries discussed in the previous topics were based on a single table. But the Join queries in DataEngine are always many-to-one, which corresponds to the common "star schema" convention in data analytics. So there is one main table and one or more 'attached' tables linked to the main table. The Join result contains the same number of rows as the main table. Every joined row contains a value for each main table column included in the result (not necessarily all columns are included, although including all columns is a common case) and a value for each included column of each linked table (usually only some of the columns of linked tables are included).

Defining a query to perform join operation

This section describes how a user can formulate a DataEngine query to perform join operation on base tables/query result tables. The join operation has been implemented as the join method in the Workspace class of the C1.DataEngine assembly, which is invoked while defining the query used to perform the join operation. This method accepts the main table as the first parameter, and the list of fields from the secondary table as the second parameter.

Consider an example where a user wants to import the data from the “Invoices” and “Products” database tables to the DataEngine base tables named “Invoices” and “Products” (Refer Connect DataEngine to Database topic for more details) . If the user wishes to find out the total sales value for each of the product categories, it can be done by joining the Products table (as it contains the product category) to the Invoices table, and later perform aggregation operation to find the category-wise sales.

To implement the operation described above, you need to join the two tables, by defining the following query using the join method of the Workspace class, to perform the join operation:

// Retrieve the main table
dynamic invoice = workspace.table("Invoices");

// Retrieve the secondary table for use in a join query
dynamic products = workspace.table("Products");

// Create a join from the main table (Invoices) to the secondary table (Products)
dynamic join = workspace.join(invoice, new
{
    category = products.CategoryID | invoice.ProductID == products.ProductID,
});

As illustrated above, main table is specified in the first parameter of the join method. In the second parameter, to the left of | separator, we specify the fields that should be included from the secondary table, that is,‘Products’. Note that addition operator can be used to include more than one fields from the secondary table. After the | separator, the join condition is specified. A linking (join) condition must be an equality expression between one or more columns from each side (they are usually called key columns). The assignment statement specifies a property of the anonymous type.

Once the two tables are joined, you can use the results of the above join query to formulate another query that can aggregate the data using the Sum method of the Op class as shown below:

// Derive another query from the join query above, to find the sales by category
dynamic query = workspace.query("SalesByCategory", new
{
    join.CategoryID,
    Sales = Op.Sum(join.ExtendedPrice)
});