Join on an optional null value

Question

I'm having trouble with the following query:

SELECT 
    job.name,
    job_element.label,
    job_element_role_hours.role,
    job_element_role_hours.hours_budgeted,
    latest_rate.hourly_rate,
    ( job_element_role_hours.hours_budgeted * latest_rate.hourly_rate ) AS line_cost
FROM
    job_element
    INNER JOIN job ON job_element.job = job.id
    INNER JOIN job_element_role_hours ON job_element_role_hours.element = job_element.id
    LEFT JOIN(
        SELECT 
            rate.*
        FROM
            rate
        LEFT JOIN rate AS newest ON (
            rate.role = newest.role
            AND COALESCE(rate.client_company, 1) = COALESCE(newest.client_company, 1)
            AND COALESCE(rate.client_group, 1) = COALESCE(newest.client_group, 1)
            AND COALESCE(rate.client_contact, 1) = COALESCE(newest.client_contact, 1)
            AND newest.date_from > rate.date_from
        )
        WHERE newest.id IS NULL
    ) AS latest_rate ON (
        latest_rate.role = job_element_role_hours.role
        AND (
            COALESCE(latest_rate.client_company, 1) = COALESCE(job.client_company, 1)
            OR latest_rate.client_company IS NULL
        )
        AND (
            COALESCE(latest_rate.client_group, 1) = COALESCE(job.client_group, 1)
            OR latest_rate.client_group IS NULL
        )
        AND (
            COALESCE(latest_rate.client_contact, 1) = COALESCE(job.client_contact, 1)
            OR latest_rate.client_contact IS NULL
        )

    )
WHERE job.id = 4

So... this query fetches a list of Elements for a Job. Each Element is broken down by "Role Hours" - an Element called "Build an HTML email", for example, might have two hours of Designer, and two hours of Developer - and then the calculates a cost based on the Hourly Rate for each Role.

The issue is where I join the latest_rate subquery back to the main query. Here's my rates table:

'CREATE TABLE `rate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_company` int(11) DEFAULT NULL,
  `client_group` int(11) DEFAULT NULL,
  `client_contact` int(11) DEFAULT NULL,
  `role` int(11) DEFAULT NULL,
  `date_from` datetime DEFAULT NULL,
  `hourly_rate` decimal(18,2) DEFAULT NULL,
  `last_edited_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

There might be several rates for a role: a "global" rate where the role is set but the client_company, client_group and client_contact fields are NULL; a company-specific rate where the role and client_company are set but everything else is NULL, and a client-specific rate where the role, client_company and client_contact is set but the client_group is NULL.

I want to get the one rate for the role that "most closely" matches the client_company, client_group and client_contact set for the Job. So if there's a Rate record with the role and client_company set (and the client_company matches the one for the Job), I want that one. Otherwise, I'll fall back to the one with NULL client_company.

My attempt at joining is clearly wrong:

AND (
     COALESCE(latest_rate.client_company, 1) = COALESCE(job.client_company, 1)
     OR latest_rate.client_company IS NULL
)

It'll return all the records that either match the client_company, or have a NULL client_company.

How can I get it to just get the one with the most matching fields?

Thanks :)

Answer 1

What you could try is have one query that basically gets all matching rates against one parameter that you absolutely want to match - seems to be role in your case.

Then you can do soemthing like:

MAX(
    CASE WHEN <conditions 1> THEN <calculation 1> END,
    CASE WHEN <conditions 2> THEN <calculation 2> END,
    ...
    )

This would give you the maximum rate possible, i.e, the rate applicable if you had the most matching fields.