| Using the Schema Designer > Views > Join Groups |
When a user query contains multiple tables (views), C1Query needs additional information to join these tables together in a single SQL statement. This information is usually provided by relationships. A relationship defines joins between two or more views, see Relationships.
However, in simpler cases you can use an easier alternative to relationships: join groups.
Often, when you need to join two tables, the join is established simply by putting into correspondence two (or more) fields, one from the first table, the other from the second table. Usually, such fields have the same name.
Instead of using relationships, you can specify join groups for certain view fields. Use the Join groups tab in the View Designer window to specify the join group name for a field. Usually, join group name is just the name of the field. However, you can use any string as a join group name.
Caution: Although join groups are intended for simpler cases, you must exercise caution using them. The alternative mechanism of joining views, relationships, gives you more control over the way C1Query joins views in queries. Using join groups, you can inadvertently allow C1Query to join views that you did not mean to be joined, if you assign their fields to the same join group by mistake. Therefore, it is recommended to avoid using join group unless you thoroughly understand how they work.
Two fields with identical non-empty join group names can be joined by C1Query even if there is no relationship joining their corresponding views.
More exactly, there are two join group names for each field: foreign join group and primary join group. Two fields, t1.f1 and t2.f2, can be joined together by the C1Query SQL generation algorithm, if the foreign join group name of t1.f1 equals the primary join group of t2.f2. The resulting SQL statement:
| Example Title |
Copy Code
|
|---|---|
…FROM… t1.f1 LEFT JOIN t2.f2 ON t1.f1 = t2.f2… |
|
So, if you want to join two views using a common field (actually, two fields, a field in the first view and a field in the second view), set the first (main) field's foreign join group name and the second (dependent) field's primary group name to the same string. Setting both primary and secondary join group names for a field, you allow that field to be used both on the main (left) and on the dependent (right) side of a join.
There is also a combo box in the Advanced tab: Use join groups as. This combo box specifies the types of the joins generated by the join groups of the view's fields when they are used on the «dependent» (right) side of a join. It can be either inner or outer join.