 <p>Try using this query:</p> <pre><code>SELECT a.name, c.company, NVL(cp.profile_value, pd.profile_value) FROM employee a INNER JOIN company c ON a.comp_id = c.comp_id LEFT JOIN company_profile cp ON c.comp_id = cp.comp_id LEFT JOIN profile_defaults pd ON cp.profile_id = pd.profile_id </code></pre> <p>This solution assumes that you have columns <code>comp_id</code> in both the <code>company_profile</code> and default <code>profile_defaults</code> tables to which you can join a record for a given company. As @PM77-1 mentioned, I use <code>NVL()</code> to first check the <code>company_profile</code> table, and default to <code>profile_defaults</code> if nothing is found in the former.</p>