When you select a relation in the Configuration Utility, the right portion of the dialog displays the properties for that relation. Only the Main page is applicable to relations.

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 query 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 properties displayed are:

• Many ("Child") Table: select which table is on the "many" side of the relationship from the drop-down list of tables. If this property contains the name of the selected table, it's disabled.

• One ("Parent") Table: select which table is on the "one" side of the relationship from the drop-down list of tables. If this property contains the name of the selected table, it's disabled.

• Simple Join: select this option if the relationship is based on matching single fields in each table; that is, the JOIN clause in a SQL statement is something like CHILD.FIELD = PARENT.FIELD.

• 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, the Configuration Utility 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 single fields; that is, the JOIN clause in a SQL statement is something like CHILD.FIELD1 = PARENT.FIELD1 AND CHILD.FIELD2 = PARENT.FIELD2. 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).

  
  

The Test button tests the relationship by doing a SELECT TOP 10 child field or fields, parent field or fields FROM child table join type parent table (for example, SELECT TOP 10 Child.Field, Parent.Field FROM Child INNER JOIN Parent) and displays the results in a grid. You can quickly see if there are any matches; if not, the relation properties likely aren't set up correctly.