Select … From … Inner Join … On …

Relational model versus Semantic arrays dml

Joins relate tables/views to combine data from multiple tables/views in a new view, based on a JOIN condition. A typical inner join in our relation model would be to relate building information to the appartments that are related to these buildings, see the following SQL statement:

Select Appartment.Id, Appartment.Street, Appartment.Number, Appartment.ZipCode
     , Appartment.Town, Building.ConstructionYear, Building.Footprint
From  Building
Inner Join  
 (Appartment Inner Join AppartmentBuildingRelation 
   On Appartment.Id = AppartmentBuildingRelation.AppartmentId)
On Appartment.Id = AppartmentBuildingRelation.AppartmentId 

resulting in the following data:

The first 5 attributes result from the Appartment table, the last 2 attributes from the Building table. The AppartmentBuildingRelation table is used to relate the tables in the Join condition.

The resulting view has the same number of rows as the AppartmentBuildingRelation table (as the combination of the AppartmentId/BuildingId in this table is unique).

GeoDMS

A relation in the relational model is defined as a subset of the Cartesian product of n domains. The result of the inner join is a relation and therefore also such a subset. The resulting domain-unit the GeoDMS of an inner join SQL statement can be configured with the combine function (to create the Cartesian product) and the subset (to make the relevant subset), see example 2.

In this case the subset domain is already configured in the GeoDMS. As mentioned, the resulting view from the inner join has the same number of rows and sequence as the AppartmentBuildingRelation table. Therefor the AppartmentBuildingRelation domain unit is the domain of the result, see example 1.

Example 1, resulting domain unit already configured (the AppartmentBuildingRelation domain unit is configured in a src container)

unit<uint32> resultdomain := src/AppartmentBuildingRelation
{
  attribute<string> AppartmentId := src/AppartmentBuildingRelation/AppartmentId;
  attribute<string> BuildingId   := src/AppartmentBuildingRelation/BuildingId;
  attribute<string> Street       := rjoin(AppartmentId, src/Appartment/id, src/Appartment/Street);
  attribute<uint32> Number       := rjoin(AppartmentId, src/Appartment/id, src/Appartment/Number);
  attribute<string> ZipCode      := rjoin(AppartmentId, src/Appartment/id, src/Appartment/ZipCode);
  attribute<string> Town         := rjoin(AppartmentId, src/Appartment/id, src/Appartment/Town);

  attribute<units/Year> ConstructionYear := rjoin(BuildingId, src/Building/id, src/Building/ConstructionYear); 
  attribute<units/m2>   Footprint        := rjoin(BuildingId, src/Building/id, src/Building/Footprint);
}

The rjoin function is used to relate attributes of two domains, Appartments and Building, using a third domain: AppartmentBuildingRelation.

Example 2, resulting domain unit is configured with combine and select_with_org_rel functions

unit<uint32> CartesianProduct := combine(src/Appartment,src/Building)
{
   attribute<string> AppartmentId  := src/Appartment/id[nr_1];
   attribute<string> BuildingId    := src/Building/id[nr_2];
   attribute<bool>   JoinCondition := 
      isDefined(
         rlookup(
            AppartmentId + '_' + BuildingId
            ,  src/AppartmentBuildingRelation/AppartmentId + '_' + 
               src/AppartmentBuildingRelation/BuildingId
        )
    );
}

unit<uint32> ResultDomain := select_with_org_rel(CartesianProduct/JoinCondition)
{
   attribute<string> AppartmentId := CartesianProduct/AppartmentId[org_rel];
   attribute<string> BuildingId   := CartesianProduct/BuildingId[org_rel];
   attribute<string> Street       := rjoin(AppartmentId, src/Appartment/id, src/Appartment/Street);
   attribute<uint32> Number       := rjoin(AppartmentId, src/Appartment/id, src/Appartment/Number);
   attribute<string> ZipCode      := rjoin(AppartmentId, src/Appartment/id, src/Appartment/ZipCode);
   attribute<string> Town         := rjoin(AppartmentId, src/Appartment/id, src/Appartment/Town);

   attribute<units/Year> ConstructionYear := rjoin(BuildingId, src/Building/id, src/Building/ConstructionYear); 
   attribute<units/m2>   Footprint        := rjoin(BuildingId, src/Building/id, src/Building/Footprint);
}