Join handling

Join handling extends the mechanism of reading from the database to join multiple tables and have them related using the database join mechanism as you would in an SQL query. To do this, you need to create a join structure that contains both the table being selected and the table being joined. As with SQL, you can join as many tables as you like by adding them to the join structure. By convention, the join structures live in the Join Structures folder associated with the database.

Creating a join structure is automated using the New Structure wizard and selecting Create a database join structure. It will prompt you for the tables involved and create the structure automatically.

In the above figure we are joining each employee to their corresponding department (a many to one join). This is done by having a join structure that first inherits from the structure to select from in Tables EMPLOYEES in this case), and then has a new element (called DEPARTMENTS_JOIN) that inherits from the Single Tables Structure to which we are joining (DEPARTMENTS). The IO/Database expression for this new element uses a DatabaseJoin function which specifies the connection between the columns in the selecting structure (EMPLOYEES) and the joined structure (DEPARTMENTS).

Additional constraints on the join may be specified in the properties of the DatabaseJoin function (double click on it in the expression tab to get the properties) in a similar manner as with the DatabaseSelect function.

In the above figure we are joining from the departments to find each department's employees (a one to many join). This works exactly the same way, except that the join element (EMPLOYEES_JOIN) must be a loop (that is, have a occurs maximum times of -1).