Quote
There is a direct correlation between division & organization, AttributionID.
There is no direct correlation between employee & division or organization.
That is the problem.
Correlation is statistical. Perhaps you mean there's 1:1 mapping between organization_id and attribution_id, but there's not 1:1 mapping between employee_id & division_id or employee_id & organization_id?
But that ambiguity illustrates the problem with ordinary narrative language as a substitute for DDL. If an NDA prohibits DDL disclosure, you need to do one of two things ... create a dummy instance of the problem with suitably disguised column names and demonstration data, or hire a consultant who's allowed inside your paranoid organisation's proprietary secrets boundary.
If MySQL can't decode your query's attribution_id reference, you need to unpack the nested joins ...
JOIN (attrib_division_rl JOIN division AS tblTier1
ON attrib_division_rl.FieldID = tblTier1.RecordID) USING(AttributionID)
LEFT JOIN (
attrib_organization_rl INNER JOIN organization
ON attrib_organization_rl.FieldID = tblTier2.RecordID
) USING(AttributionID)
... to something like ...
JOIN (attrib_division_rl ON ...
JOIN division AS tblTier1
ON attrib_division_rl.FieldID = tblTier1.RecordID) USING(AttributionID)
LEFT JOIN attrib_organization_rl
ON ...
JOIN organization AS tblTier2
ON attrib_organization_rl.FieldID = tblTier2.RecordID
LEFT JOIN employee AS tblTier3
ON ...
... but without the DDL I can't correctly populate the ON clauses, you'll need to do that.