How to Create Joins Explicitly

In order to create joins explicitly, you need to use navigation in the Connection Object List by relations. When you open a table in the Connection Object List, you can navigate to its parent table by clicking the query-icon-relation icon to the right of the corresponding foreign key field.

If you drag a field or several fields from the parent table in the Connection Object List to query builder after navigating to it via a relation, query builder will generate JOIN by this relation in the FROM clause of the SELECT statement. If you have navigated to a table via several relationships in a table hierarchy, JOINs for all of the relationships used are added to the query.

To navigate back to the child table you can click the query-icon-less button in the Connection Object List header. Alternatively you can click the query-icon-arrow-down button in the Connection Object List header to open a list with all the tables, by relationships of which you have navigated to the current table, and the corresponding foreign key fields used for navigation. Then click the necessary table in the list.

To explicitly add join by a relationship to your query, perform the following steps:

1.When you want to add data from several tables, having foreign key relationships, start with the "most child table" you want to include to a query - a table, not referenced by foreign keys you want to use for joins. Click this table in the Connection Object List in order to access its fields and drag the necessary fields from it to the Result fields pane.
2.Then navigate to the necessary parent table in the following way: find the foreign key field of the child table, belonging to the foreign key you want to use for join, in the Connection Object List and click the query-icon-relation icon to the right of the foreign key field.
3.Drag the necessary parent fields to the Result fields pane. Query Builder will automatically create JOIN on the foreign key you have used for navigating to the parent table.
4.If you need to add fields from the higher level parent table, find a foreign key field of the corresponding foreign key in the current table, click the query-icon-relation icon to the right of the foreign key field to navigate to the higher level parent table. Then drag the necessary fields to the Result fields pane. Repeat this till you get to the "most parent" table you want to add the fields from to the query.

note Note

When you add a join explicitly, the joined parent table is assigned an alias based on the relationship names you have used for navigation.

To get better understanding of adding joins explicitly, you can visit the Examples of Creating Joins topic.