How to Create Joins in Queries

Joins are used to combine rows from two or more tables, based on a related column between them. In this tutorial, we provide you with two examples of how to create joins in queries.

Simple Example

First let us consider a simple example of joins 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 your connection to Salesforce.

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.

   

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

   

6. Navigate to the OwnerId field and click the 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.

   

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 check the generated SQL statement. 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 a more complex example with several tables and multiple relations in the hierarchy. To demonstrate this case, we will use SQL Server and standard sample database of Microsoft — AdventureWorks.

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

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 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 Type to filter box in the top part of the Connection Object 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 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 icon to the right of the SalesPersonId field. This opens the Employee table.
8. Navigate to the ContactId field and click the 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 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.

    

11. Click the SalesOrderHeader table.
12. Navigate to the CustomerId field and click the 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.

    

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