Relating 2 tables with possible wildcards?
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:


Managed, Category, and Customer
Managed and Category
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;