SQL Query does not return empty fields when using inner Join

102 views Asked by At

I'm currently working on a Microsoft Access query to populate a template in Enterprise Architect. The query is mostly functional, but I'm facing an issue when retrieving obj6 based on the value of the 'Selected Way.' Specifically, the query fails to return any result when the 'Selected Way' is empty. Ideally, it should return all other values even when this field is left empty.

SELECT obj4.Name,
       obj2.Name,
       obj3.Name,
       obj5.Name,
       obj6.Name
FROM   ((((((((((t_object obj
                 INNER JOIN t_connector con
                         ON ( con.end_object_id = obj.object_id
                              AND con.stereotype = 'Sterotype' ))
                INNER JOIN t_object obj4
                        ON ( con.start_object_id = obj4.object_id ))
               INNER JOIN t_objectproperties objp
                       ON ( obj4.object_id = objp.object_id
                            AND objp.property = 'Inbound' ))
              LEFT JOIN t_object obj2
                     ON ( objp.Value = obj2.ea_guid ))
             INNER JOIN t_objectproperties objp2
                     ON ( obj4.object_id = objp2.object_id
                          AND objp2.property = 'Outbound' ))
            LEFT JOIN t_object obj3
                   ON ( objp2.Value = obj3.ea_guid ))
           INNER JOIN t_objectproperties objp3
                   ON ( obj4.object_id = objp3.object_id
                        AND objp3.property = 'Tool' ))
          LEFT JOIN t_object obj5
                 ON ( objp3.Value = obj5.ea_guid ))
         INNER JOIN t_objectproperties objp4
                 ON ( obj4.object_id = objp4.object_id
                      AND objp4.property = 'Selected way' ))
        INNER JOIN t_objectproperties objp5
                ON ( obj4.object_id = objp5.object_id )
                   AND objp4.Value = objp5.property)
       INNER JOIN t_object obj6
               ON ( obj6.ea_guid = objp5.value )
WHERE  obj.object_id = #userImput#
       AND objp4.Value = objp5.property
ORDER  BY obj4.Name ASC 

Example retrun of the query

inner join t_objectproperties objp4
 on (obj4.object_id = objp4.object_id
 and objp4.property = 'Selected way'))
inner join t_objectproperties objp5
 on (obj4.object_id = objp5.object_id)
 AND objp4.Value = objp5.property)
inner join t_object obj6
 on (obj6.ea_guid = objp5.value)
WHERE obj.object_id = #userImput# AND objp4.Value = objp5.property

I've attempted to simplify and use a left join for the 'Selected Way,' but I'm struggling to achieve the desired result. Can someone help me with this?

Thank you!

0

There are 0 answers