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
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!