JoinDef Rules
To be linked with a JoinDef, two or more tables must have at least one column with the same data type and one or more common values in those columns. For example, if a table named Projects and a table named Departments both have a column named Dept, which contains names of departments, the common values in the two Dept columns join the two tables in the JoinDef, as shown in the following figure:
While the two columns in this example have the same name, this is not required for a JoinDef.
If you think of tables as two‑dimensional grids of columns and rows, then JoinDefs are the third dimension. JoinDefs can be described as corridors linking the columns of two tables.
JoinDefs must follow these rules:
• The two join columns need not have the same name.
• Join column pairs must either be of the same basic data type, such as an integer column joined to another integer column, or be of data types that are coercible to one another.
• Except in a Master/Detail join, the data values in all the join columns must be identical in the joined tables in order for a query to return that row.
• A JoinDef can join a total of 10 tables, either Master or Detail, that together total no more than 600 columns.
• You can specify up to 50 columns in each table as join columns.
• You can create a JoinDef for a single table, which has several advantages over using the table itself as a query target. See
Single-Table JoinDefs (see page
Single-Table JoinDefs).
The figure below shows some examples of JoinDefs. As long as you do not exceed the total limit of 10 tables, you can join one table to as many other tables as you desire. However, any given JoinDef can have only one Master‑Detail join.
Queries only return those records that have identical data values in all the join columns. Thus, if the Projects and Tasks tables are joined by the Project column and they share only the single project name of Release6, a query returns only one row of data from each table. If they share the project names Release6 and Popup, then a query returns two rows from each table.
