LoadComplete includes database variables that you can use to parameterize requests with values from a database. You can set up a database variable to retrieve data from the database using a custom SQL query. In this query, you can use JOIN clauses to combine data from two or more tables. Join means that the query will retrieve only those fields and records from a table that have matching values in another table.
To visually configure properties of a table join, you can use the Edit Join dialog. The dialog opens when you select Edit from the context menu of a join line in the SQL Designer.
The two Table boxes display the names and aliases of the joined tables (if a table has an alias, the table’s name is enclosed in parentheses). The Column boxes show the columns which are used for the join. All of these values (table names and column names) are read-only. However, you can change the join expression by selecting the desired condition from the drop-down list that resides between the column names.
Using the two Add all rows check boxes you can specify how the tables will be joined and which query the Designer will generate. The join means the query will analyze the table rows and include only those rows and data that have matching values in another table. The following table explains how the check boxes work. We assume that the joined tables are named Table1 and Table2:
| Condition | Description |
|---|---|
| Add all rows of Table1 is clear Add all rows of Table2 is clear |
The query will include only those rows of Table1 that have matching rows in Table2. The same is for Table2: the query will include only those rows of Table2 that have matching rows in Table1. This condition corresponds to the INNER JOIN clause of the SQL SELECT comment. The Designer will generate this clause for your query. For instance: |
| Add all rows of Table1 is selected Add all rows of Table2 is clear |
The query will include all rows of Table1 regardless of whether they have matching values in Table2. As for Table2, the query will include only those rows which have the appropriate rows in Table1. In other words, when this condition is active, Table1 acts as a master table and Table2 is considered to be a detail table. This condition corresponds to the LEFT OUTER JOIN clause of the SQL SELECT command. The Designer will generate this clause for your query, for instance: |
| Add all rows of Table1 is clear Add all rows of Table2 is selected |
This condition is contrary to the previous condition. The query will include all rows of Table2 and only those rows of Table1, which have the matching rows in Table2. This condition corresponds to the RIGHT OUTER JOIN clause of the SQL SELECT command. The Designer will generate this clause for your query, for instance: |
| Add all rows of Table1 is selected Add all rows of Table2 is selected |
This condition will include all the rows from both tables. If a table does not contain the matching rows in another table, the query fields that correspond to columns of the other table will contain null values. This condition corresponds to the FULL OUTER JOIN clause of the SQL SELECT comment. The Designer will generate this clause for your query. For instance: |
Show ExampleTo save the changes you made to the join properties in the dialog, click OK. To close the dialog without saving any changes, click Cancel.