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.
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.|
|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 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 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's 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 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. |
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.