Relating 2 tables with possible wildcards?

Question

I am having trouble getting the desired results when trying to relate the following tables.

NULL in the following table essentially means that if it is not an exact match with another row then choose this one. A wildcard if you will. I have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

tblRateCategory

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryID if Category and/or Customer has match. So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Select JobNumber, RateCategoryID 
From tblJob J
Inner Join tblRateCategory RC
ON isnull(RC.Category,J.Category)=J.Category and
RC.Managed=J.Managed and isnull(RC.Customer,J.Customer)

Results from above query:

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50

Answer 1

Tables

DECLARE @RateCategory AS table
(
    RateCategoryID integer PRIMARY KEY NONCLUSTERED,
    Managed bit NOT NULL,
    Customer varchar(10) NULL,
    Category char(1) NULL,

    UNIQUE CLUSTERED (Managed, Category, Customer)
);

DECLARE @Job AS table
(
    JobNumber integer PRIMARY KEY,
    Managed bit NOT NULL,
    Customer varchar(10) NOT NULL,
    Category char(1) NOT NULL
);

Sample Data

INSERT @RateCategory
    (RateCategoryID, Managed, Customer, Category)
VALUES
    (10, 0, NULL, NULL),
    (20, 0, NULL, 'A'),
    (30, 0, 'CustC', 'A'),
    (40, 1, 'CustC', 'A'),
    (50, 1, NULL, 'A'),
    (60, 0, NULL, 'B');

INSERT @Job
    (JobNumber, Managed, Customer, Category)
VALUES
    (1, 0, 'CustA', 'C'),
    (2, 0, 'CustB', 'D'),
    (3, 0, 'CustC', 'A'),
    (4, 1, 'CustC', 'A'),
    (5, 0, 'CustD', 'A'),
    (6, 0, 'CustE', 'B'),
    (7, 1, 'CustF', 'A');

Solution

The logic here is to find a match on:

1. Managed, Category, and Customer
2. Managed and Category
3. Managed alone

Each step only occurs per job if the previous step did not find a match. The initial CROSS APPLY ensures a row is only returned if one of the steps found a match.

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY 
(
    SELECT
        RateCategoryID =
            COALESCE
            (
                -- Preferred order of matches
                MatchAll.RateCategoryID,
                Match2.RateCategoryID,
                Match1.RateCategoryID
            )
    FROM 
    (
        SELECT NULL
    ) AS Dummy (NotUsed)
    OUTER APPLY
    (
        -- Complete match
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- Conditions for this step
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer
    ) AS MatchAll
    OUTER APPLY
    (
        -- Match Managed and Category
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL
    ) AS Match2
    OUTER APPLY
    (
        -- Match Managed only
        SELECT TOP (1)
            RC.RateCategoryID
        FROM  @RateCategory AS RC
        WHERE
            -- No previous match
            MatchAll.RateCategoryID IS NULL
            AND Match2.RateCategoryID IS NULL
            -- Conditions for this step
            AND RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match1
) AS RC;

Results

Demo

Stack Exchange Data Explorer

Execution Plan

Note the Filter operators have startup predicates. This means a seek is only performed if the previous seek did not find a row.

Alternative

SELECT
    J.JobNumber,
    RC.RateCategoryID
FROM @Job AS J
CROSS APPLY 
(
    -- Just the highest priority match per job
    SELECT TOP (1)
        Match.RateCategoryID
    FROM
    (
        -- Priority 1: Match Managed, Category, and Customer
        SELECT
            RC.RateCategoryID,
            [Priority] = 1
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer = J.Customer

        UNION ALL

        -- Priority 2: Match Managed and Category
        SELECT
            RC.RateCategoryID,
            [Priority] = 2
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category = J.Category
            AND RC.Customer IS NULL

        UNION ALL

        -- Priority 3: Match Managed
        SELECT
            RC.RateCategoryID,
            [Priority] = 3
        FROM @RateCategory AS RC
        WHERE
            RC.Managed = J.Managed
            AND RC.Category IS NULL
            AND RC.Customer IS NULL
    ) AS Match
    ORDER BY
        Match.[Priority] ASC
) AS RC;