Using joined tables
Glossary Item Box
JOIN types of joined tables
The JOIN operator is used when adding a column from a schema (other than the root schema) to the query. The LEFT OUTER joining type is used by default. While adding columns from a non–root schema, EntitySchemaQuery enables you to specify the query joining type for this schema. To do this, simply specify the name of the column in the following format:
[Special character connection type] [Column name]
Table 1 – JOIN types of joined schemas.
| Joining type. | Special character for joining type | Column name example |
|---|---|---|
| INNER JOIN | "=" | "=Name" |
| LEFT OUTER JOIN | ">" | ">Name" |
| RIGHT OUTER JOIN | "<" | "<Name" |
| FULL OUTER JOIN | "<>" | "<>Name" |
| CROSS JOIN | "*" | "*Name" |
Example
Adding columns to a query using various schema joining types.
// Creating the query instance with the City root schema. var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City"); // A Country schema with the LEFT OUTER JOIN connection type will be added to the query. esqResult.AddColumn("Country.Name"); // A Country schema with the INNER JOIN connection type will be added to the query. esqResult.AddColumn("=Country.Name"); // Two schemas will be connected to the query: // 1) The Country schema with the LEFT OUTER JOIN connection type; // 2) The Contact schema with the RIGHT OUTER JOIN connection type. esqResult.AddColumn(">Country.<CreatedBy.Name"); // Text of the resulting sql-query (MS SQL): // SELECT // [Country].[Name] [Country.Name], // [Country1].[Name] [Country1.Name], // [CreatedBy].[Name] [CreatedBy.Name] // FROM // [dbo].[City] [City] // LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId]) // INNER JOIN [dbo].[Country] [Country1] ON ([Country1].[Id] = [City].[CountryId]) // LEFT OUTER JOIN [dbo].[Country] [Country2] ON ([Country2].[Id] = [City].[CountryId]) // RIGHT OUTER JOIN [dbo].[Contact] [CreatedBy] ON ([CreatedBy].[Id] = [Country2].[CreatedById])
Overlapping access rights to joined schemas
If the root schema of the query is administered by records and the query contains joinable schemas, then the access rights of the current user can be applied to them. Table 2 shows all the possible options for overlapping access rights to the joined schemas. These values correspond to the members of the Terrasoft.Core.DB.QueryJoinRightLevel enumeration.
Table 2 – Options for overlapping access rights to joinable query schemas
| Enumeration member value | Order of access rights overlap |
|---|---|
| EnabledAlways = 0 | Always enable access rights overlap. |
| EnabledForAdditionalColumns = 1 | Apply only if the query uses columns of the related schema that are other than the primary column and primary display column. |
| Disabled = 2 | Do not overlap access rights. |
The order of access rights overlap is determined by the JoinRightState property of a query. The default value of this property is determined by the QueryJoinRightLevel system setting, which pulls its values according to table 2. If the value of this system setting is not specified, then its default value is assumed to be [EnabledForAdditionalColumns].
