Join

Understanding Tables in RestBI

A Join defines the relationship between two tables in the data model, allowing you to query and combine data across multiple tables. Joins are essential for constructing complex queries that require data from more than one table.

Overview & Usage

The Join object specifies how two tables are related in the database, by defining one or more join conditions. These conditions determine how rows from one table match rows from another, enabling the combination of data across tables

Joins are used to link tables together within a model. Each join specifies the relationship between two tables using one or more join conditions. The join conditions determine how data is matched between the tables, enabling complex queries that pull data from multiple sources.

Example

Here is an example of how to define a Join object:

const productToSubcategoryJoin: Join = {
    id: '1',
    table1: 'Product',
    table2: 'ProductSubcategory',
    clauses: [
        { column1: 'ProductSubcategoryID', column2: 'ProductSubcategoryID', operator: '=' }
    ],
    joinType: 'INNER JOIN'
};

const orderToCustomerJoin: Join = {
    id: '2',
    table1: 'SalesOrderHeader',
    table2: 'Customer',
    clauses: [
        { column1: 'CustomerID', column2: 'CustomerID', operator: '=' }
    ],
    joinType: 'INNER JOIN'
};

const productToCategoryJoin: Join = {
    id: '3',
    table1: 'ProductSubcategory',
    table2: 'ProductCategory',
    clauses: [
        { column1: 'ProductCategoryID', column2: 'ProductCategoryID', operator: '=' }
    ],
    joinType: 'LEFT JOIN'
};

Join Reference

Property	Description
id	A unique identifier for the join.
table1	The name of the first table in the join relationship.
table2	The name of the second table in the join relationship.
clauses	An array of JoinClause objects, each representing a condition for how the tables should be joined.
joinType	The type of join to perform (INNER JOIN, LEFT JOIN, etc.). This is optional; the default is usually INNER JOIN.

JoinClause Reference

Property	Description
column1	The name of the column from the first table in the join.
column2	The name of the column from the second table in the join.
operator	The operator used in the join condition (e.g., =, <>, etc.).

See Also

• Tables: Learn more about how tables are defined and used in the model.

• Models: Overview of how joins integrate into the broader data model.