Example of Creating Joins

Simple Example

First let us consider a simple example with two tables — parent and child. For this let’s query the number of Salesforce accounts owned by users for each user. Since there are a number of relations between the account and user tables, we need to specify the relation to join the tables by. In order to create this query, you need to perform the following steps:

  1. Click +NEW in the top menu.
  2. Click Builder under QUERY.
  3. In the Select connection list, select Salesforce as your connection.
  4. First, we need to open the child table. In our case it is the Account table. Click the Account table in the Connection Object List. You can quickly find it by typing “Account” in the Type to filter box in the top part of the Connection Object List.

    All Tables list

  5. Drag the Records count pseudo-field from the Connection Object List to the Result Fields pane.

    Records Count field

  6. Navigate to the OwnerId field and click the Relation button icon to the right of this field (you can use the Type to filter box to quickly find it as well). The User table will open.
  7. Drag the Name field from the Connection Object List to the Result Fields pane.

    Relations Query

That’s all, our query is ready. It correctly joins the Account and User tables by the OwnerId field. You can switch to the SQL view by clicking SQL on the Query toolbar and see the generated SQL statement to make sure. Note that the User table is assigned with an alias Owner, corresponding to the name of the relation between the tables.

Complex Example

Now let us consider creating joins using a more complex example with several tables and multiple relations in the hierarchy. To demonstrate this case, we will use SQL Server and standard Microsoft’s sample database AdventureWorks.

We will query the number of orders by assigned employee and by customer type. Let us take a look at the tables, participating in the query.

Relations Diagram

We will take a number of orders from the SalesOrderHeader table, and a customer type from the Customer table. Employee names are stored in the Contact table. However, we cannot simply add fields from the Contact table, because in this case the direct foreign key FK_SalesOrderHeader_Contact_ContactID by the ContactID field will be used. The tables must be joined all the way via SalesPerson and Employee tables by the corresponding relations.

In order to create this query, you need to perform the following steps:

  1. Click +NEW in the top menu.
  2. Click Builder under QUERY.
  3. In the Select connection list, select Adventure Works SQL Server database as your connection.
  4. When we look at the table relation, we see that the “most child” table in our case is SalesOrderHeader. In our query there will be no table, for which SalesOrderHeader is a parent table. So click SalesOrderHeader in the Connection Object List. You can quickly find it by typing “SalesOrderHeader” in the Type to filter box in the top part of the Connection Object List.

    All Tables list

  5. Drag the Records count pseudo-field from the Connection Object List to the Result Fields pane.
  6. Navigate to the SalesPersonId field and click the Relation button icon to the right of this field (you can use the Type to filter box to quickly find it as well). The SalesPerson table will open.
  7. Click again the Relation button icon to the right of the SalesPersonId field. This opens the Employee table.
  8. Navigate to the ContactId field and click the Relation button icon to the right of this field. The Contact table will open.
  9. Drag the FirstName and LastName fields from the Connection Object List to the Result Fields pane.
  10. Now we want to add the CustomerType column from the Customer table to the Query. Let us navigate back to our SalesOrderHeader table, which contains a foreign key to the Customer table. For this, click the Navigation button button in the Connection Object List header. The breadcrumbs list with the tables we have navigated through is displayed with the foreign key fields used for navigation.

    Breadcrumbs list

  11. Click the SalesOrderHeader table.
  12. Navigate to the CustomerId field and click the Relation button icon to the right of this field (you can use the Type to filter box to quickly find it as well). The Customer table will open.
  13. Drag the CustomerType field from the Connection Object List to the Result Fields pane.

    Query Complex Example

That is all, our query is ready. It correctly joins the queried tables. You can switch to the SQL view by clicking SQL on the Query toolbar and see the generated SQL statement to make sure that everything is okay. Note that the joined tables have aliases generated by concatenating the foreign key names used for joins.