Multiple Table Joins
To query from more than one output table use the Aurora Output DB Query Builder. However, an additional step is required while constructing the query. That step involves defining the relationships required to join records from one table with those of another.
Select the columns to be joined by clicking the column name in one table and dragging to the column name in another table. Note that it is possible to include as many tables in the join definitions as needed.
NOTE: Using the Query Builder to create joins automatically creates indexes on the columns used in the join and is the recommended method for implementing joins. Aurora creates the indexes by looking for the “<space>On<space>” in the join syntax. If that syntax is not used in the join (i.e., “<tab>On<tab>”) then Aurora will not create the indexes and an extremely slow query may result.
Advanced users that write their own code, in the query generation area at the bottom of the form, should use caution and use the required “<space>On<space>” syntax for joins. Check the syntax created by the Query Builder with simple joins for examples of valid syntax for joins. Users creating more complex queries should create indexes in the SQL code as appropriate (see Creating Indexes for Queries).
In the example shown below, the output tables ResourceMonth1 and ResourceEmissionsMonth1 are joined through the Condition, ID, and Time_Period columns. With three emission types included in the ResourceEmissionsMonth Output table, this will produce three records for each resource contained in the ResourceMonth Output table. Note that in this example, fields representing the same variables are named identically. That is not always guaranteed to be the case.
Additionally, in setting up the joins, caution should be exercised to guarantee that all one-to-one relationships between tables have been defined or the potential exists to create many extra records when the join is executed. For example, if the Time_Period field were not included in the example joins, then for each record extracted from the ResourceMonth table thirty-six records would be created from the ResourceEmissionsMonth table, instead of three.
After all joins have been defined, select Create Table with SQL then Exit from the form toolbar.
Productivity Tools
Multiple Table Joins
