Three table join, subquery, or something easier?

I was wondering if someone could help me with this.

This is a quick representation of a much more complicated schema. I am looking to get this implementation which is part of a larger stored proc.

So basically I am looking to obtain a result set that includes ClientId, Company Name, Setting1, Setting2 and Setting3.

So if a Company Id does not exist in the Settings Override table, then they subscribe to their respective default settings. But if they DO have an ID in the SettingsOverride table then the result set should return those settings.

So for this example the Result Set would look like this:

Answer

You can join twice:

select
    c.clientId,
    c.companyName,
    coalesce(so.setting1, df.setting1) setting1,
    coalesce(so.setting2, df.setting2) setting2,
    coalesce(so.setting3, df.setting3) setting3
from company c
inner join defaul_settings cs on cs.clientId = c.clientId
left join settings_override so on so.companyId = c.companyId

The critical part is to left join table settings_override, so companies that have no override are not excluded from the resultset. Then, you can use colasce() to use the default when there is no override.