Not sure if this is what you want. It all depends on what you want to do with the data from the additonal tables.
LEFT OUTER JOIN E__EMPLOYEE e
ON [result].EMPLOYEEID IS NOT NULL
AND e.EMPLOYEEID = [result].EMPLOYEEID
LEFT OUTER JOIN S_ORGANISATION o
ON [result].SUPPLIERID IS NOT NULL
AND o.SUPPLIERID = [result].SUPPLIERID
LEFT OUTER JOIN C_TENANCY t
ON [result].TENANCYID IS NOT NULL
AND t.TENANCYID = [result].TENANCYID
This will return the columns from each table and will contain nulls where the ID is null or the matching rows
You can then select what data you require using eith CASE or COALESCE
Really need more info.
Far away is close at hand in the images of elsewhere.
Anon.