sql server – Join to get most specific record for tenant without PIVOT


I have a Settings table and a Tenant table.
There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities’.

Account 1
   ---> Company 1
          ---> Facility 1   
          ---> Facility 2
   ---> Company 2
          ---> Facility 3   
          ---> Facility 4

They may have a default setting that applies to their entire account….

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|     (null) |    (null) |         1 |            5 |

…except they have one override for Facility 3 that only applies to Facility 3, all other facilities will use the default setting value at the account level.

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          3 |    (null) |         1 |            6 |
   

I want to create a join between them to get the most specific setting for each tenant. Most specific is defined as the Setting record that matches the Tenant‘s FacilityId is more specific than a match on CompanyId which is more specific than a match on AccountId and finally, if no match is found, use the setting that has NULL for all 3 values.

I do not want to use the PIVOT feature as the code uses Entity Framework and LINQ and there is no LINQ to SQL for PIVOT. Basically need simple SQL that you could create a view for…so no temp tables, etc. Not looking for a stored proc solution if at all possible.

SQLFiddle

Table: Settings

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|     (null) |         2 |         2 |            7 |
|     (null) |         1 |         1 |            4 |
|     (null) |    (null) |         2 |            6 |
|     (null) |    (null) |         1 |            3 |
|     (null) |    (null) |    (null) |            2 |

Table: Tenants

| FacilityId | CompanyId | AccountId |
|------------|-----------|-----------|
|          1 |         1 |         1 |
|          2 |         2 |         2 |
|          3 |         3 |         3 |

So join on these would have this desired output:

| FacilityId | CompanyId | AccountId | SettingValue |
|------------|-----------|-----------|--------------|
|          1 |         1 |         1 |            5 |
|          2 |         2 |         2 |            7 | --> this account would match to a setting value of 6 or 7, but the 7 value matches more specifically
|          3 |         3 |         3 |            2 | --> there is no match on Facility, Company, or Account so match to all nulls.

In code, I am doing the following to get the most specific Setting for a given Tenant, but I now need to do this for a large set of Tenant data and hence want to do it by a SQL Join. For those unfamiliar with LINQ the double pipe (||) is equivalent to OR.

private SettingViewModel GetSettingBy(string strKey)
{
    var allSettings = GetAllSettings();
    var settingQuery = allSettings.Where(x => x.SettingKey == strKey);

    if (_accountCompanyFacilityViewModel.AccountId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.AccountId == _accountCompanyFacilityViewModel.AccountId || x.AccountId == null));
    }

    if (_accountCompanyFacilityViewModel.CompanyId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.CompanyId == _accountCompanyFacilityViewModel.CompanyId || x.CompanyId == null));
    }

    if (_accountCompanyFacilityViewModel.FacilityId.HasValue)
    {
        settingQuery = settingQuery.Where(x => (x.FacilityId == _accountCompanyFacilityViewModel.FacilityId || x.FacilityId == null));
    }

    var setting = settingQuery
            .OrderByDescending(x => x.FacilityId)
            .ThenByDescending(x => x.CompanyId)
            .ThenByDescending(x => x.AccountId)
            .FirstOrDefault();
            
    return setting;
}

Here’s the SQL Fiddle for the Answer