Problem

A SQL statement is needed, that returns a list of all link relations with start and end object name. Additionally a tagged value (here it is called ‚Object identifier‘) should be appended

Approach

Usage of MDB Plus

Solution

Note: If you do not need tagged values, you can customize the following Access / .eap-File Statement in the way that you reduce it by leaving out the last 2 LEFT JOINs (and don’t forget to remove the Columns from the Select clause).

SELECT      objstart.[Object_ID] As EAIDSource,
            objstart.[Name] AS EANameSource,
            objpropstart.[Value] AS DoorsSoruceObjectID,
            tconn.[Connector_ID] AS EAConnectorID,
            tconn.[Direction] AS EADirection,
            tconn.[Connector_Type] AS EAConnectorType,
            tconn.[Stereotype] AS EAStereoType,
            objend.[Object_ID] AS EAIDTarget,
            objend.[Name] AS EANameTarget,
            objpropende.[Value] AS DoorsTargetObjectID
FROM                                                                                          
(((                                                              
[t_connector] tconn LEFT JOIN [t_object] objstart ON tconn.[Start_Object_ID]=objstart.[Object_ID])
                    LEFT JOIN [t_object] objend ON tconn.[End_Object_ID]=objend.[Object_ID])                                                                           
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropstart ON tconn.[Start_Object_ID]=objpropstart.[Object_ID] )
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropende ON tconn.[End_Object_ID]=objpropende.[Object_ID]