How to Create Joins
In order to create joins, 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 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, click the button in the Connection Object List header. Alternatively, you can click the 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 add join by a relationship to your query, perform the following steps:
- 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.
- 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 icon to the right of the foreign key field.
- 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.
- 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 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 until you get to the “most parent” table you want to add the fields from to the query.
When you add a join, the joined parent table is assigned with an alias based on the relationship names you have used for navigation.
To get better understanding of adding joins, you can read Examples of Creating Joins topic.