Join to get most specific record for tenant without PIVOT

Question

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

Answer 1

Thanks for the great data setup. Here's one way to get these hits in SQL. As Aaron mentioned, this can be a CTE without any varibles.

WITH cte AS (
                SELECT  t.AccountId
                        ,t.CompanyId
                        ,t.FacilityId
                        ,CASE
                             WHEN s.AccountId IS NOT NULL THEN 1
                             ELSE 0
                         END + CASE
                                   WHEN s.CompanyId IS NOT NULL THEN 1
                                   ELSE 0
                               END + CASE
                                         WHEN s.FacilityId IS NOT NULL THEN 1
                                         ELSE 0
                                     END AS HitCount
                        ,s.SettingValue
                FROM    dbo.Tenant AS t
                        LEFT OUTER JOIN dbo.Settings AS s ON t.FacilityId LIKE ISNULL(CAST(s.FacilityId AS VARCHAR), '%')
                                                             AND   t.CompanyId LIKE ISNULL(
                                                                                              CAST(s.FacilityId AS VARCHAR)
                                                                                              ,'%'
                                                                                          )
                                                             AND   t.AccountId LIKE ISNULL(
                                                                                              CAST(s.AccountId AS VARCHAR)
                                                                                              ,'%'
                                                                                          )
            )
SELECT  t.AccountId
        ,t.CompanyId
        ,t.FacilityId
        ,t.SettingValue
FROM    cte AS t
        CROSS APPLY (
                        SELECT  MAX(t2.HitCount) AS MaxHits
                        FROM    cte AS t2
                        WHERE   t.AccountId = t2.AccountId
                                AND t.CompanyId = t2.CompanyId
                                AND t.FacilityId = t2.FacilityId
                    ) AS hc
WHERE   1 = 1
        AND hc.MaxHits = t.HitCount;