Examples of Creating Joins

ExpandedToggleIcon        Simple Example

First let's show how to do it on 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 explicitly specify the relation to join the tables by. In order to create this query, you need to perform the following steps:

1.Open the Query page by clicking Query in the menu on the left side of the page.
2.Click the New Builder button in order to create a new query.
3.In the Select connection list select your connection to Salesforce.
4.First we need to open the child table. In our case this is the Account table. Click the Account table in the Connection Object List. You can quickly find it by typing "Account" in the search box in the top part of the Connection Object List.
 
query-tutorial-click-account
5.Drag the Record count pseudo-field from the Connection Object List to the Result fields pane.
 
query-tutorial-drag-count
6.Navigate to the OwnerId field and click the query-icon-relation icon to the right of this field (You can use the search 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.

query-tutorial-relations-simple-final

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 an alias Owner, corresponding to the name of the relation between the tables.

 

ExpandedToggleIcon        Complex Example

Now let's demonstrate creating joins on 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's take a look on the tables, participating in the query.

query-tutorial-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.Open the Query page by clicking Query in the menu on the left side of the page.
2.Click the New Builder button in order to create a new query.
3.In the Select connection list select your connection to Adventure Works SQL Server database.
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 search box in the top part of the Connection Object List.
 
query-tutorial-complex-child-table
5.Drag the Record count pseudo-field from the Connection Object List to the Result fields pane.
6.Navigate to the SalesPersonId field and click the query-icon-relation icon to the right of this field (You can use the search box to quickly find it as well). The SalesPerson table will open.
7.Again click the query-icon-relation icon to the right of the SalesPersonId field. This opens the Employee table.
8.Navigate to the ContactId field and click the query-icon-relation 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's navigate back to our SalesOrderHeader table, which contains a foreign key to the Customer table. For this click the query-icon-arrow-down 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.
 
query-connection-object-list-breadcrumbs
11. Click the SalesOrderHeader table.
12. Navigate to the CustomerId field and click the query-icon-relation icon to the right of this field (You can use the search 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-tutorial-3-big

That's 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. Note that the joined tables have aliases generated by concatenating the foreign key names used for joins.