This is an advanced topic that requires some knowledge of databases.

In step 2 of the report wizards, if you add a field from a table that is not related in any way to the other tables involved in the report, you are asked if you want to create a relationship between the tables. If you choose No, the report is unlikely to run properly. If you choose Yes, the Create Relationship dialog appears.

Although SQL doesn't have the concept of one-to-many or child-parent relations, Stonefield Query has an easier time resolving the relationships required in a report if you specify which table is on the "many" side and which is on the "one" side of a relationship (or which is the parent and which is the child).

The settings are:

• Many ("Child") Table: select which table is on the "many" side of the relationship from the dropdown list of tables. If this setting contains the name of the table you just added to the report, it's disabled.

• One ("Parent") Table: select which table is on the "one" side of the relationship from the dropdown list of tables. If this property contains the name of the table you just added to the report, it's disabled.

• Simple Join: select this option if the relationship is based on matching up to five fields in each table; that is, the JOIN clause in a SQL statement is something like CHILD.FIELD = PARENT.FIELD or a little more complex like CHILD.FIELD1 = PARENT.FIELD1 AND CHILD.FIELD2 = PARENT.FIELD2.

• Child Field: select the field from the child table used in the relationship.

• Parent Field: select the field from the parent table used in the relationship. Note that when you select the child field, Stonefield Query automatically selects any parent field with the same name, since it's likely that field is the linking field.

• Complex Join: select this option for relationships that aren't based on matching fields or there are more than five fields involved. Enter the expression for the relationship. Field names should be fully aliased (that is, include the name of the table). If either the table or field name contains illegal SQL characters (such as spaces) or SQL keywords, place delimiters around the table or field name.

• Join Type: select which type of join to use: inner, left outer, right outer, or full. An inner join only selects those parent ("one") records with at least one matching child ("many") record. In Stonefield Query, the child ("many") table is always on the left side of a join, so a left outer join means that you want child records regardless of whether a parent record exists or not, and a right outer join (more common) means you want parent records whether a child record exists or not. A full join gives records from both tables regardless of whether there are matching records in either table.

• Join Weight: some applications, such as accounting systems, have large numbers of tables and complex relationships between them. As a result, there may be more than one "path" from one table to another, indirectly related, table. Consider the relationships shown below. There are two ways to get from Table A to Table D. However, if the preferred path is through Table B, you can tell Stonefield Query that by setting the join weight for the relationship between Table C and Table D to a higher value (the lower the value, the more important the join).

  

• Test: this button tests the relationship by doing a SELECT child field or fields, parent field or fields FROM child table join type parent table (for example, SELECT Child.Field, Parent.Field FROM Child INNER JOIN Parent) and displays the first ten results in a grid. You can quickly see if there are any matches; if not, a dialog displays possible reasons why the relationship failed.