Hi, me again

I'm having trouble with the query below. The goal is to pull a list of Elements for a Job, broken down by Job Role, with a calculation of the cost for each Element.

My trouble is coming from the way hourly rates are set for different job roles. It's not just "one role: one hourly rate". The rate for a particular role may vary according to the client company, client group (a "group" just being a division within a company), or even individual client contact.

So my Rates table might, for example, have several records matching a particular role id: one 'global' rate where the company, group and contact are all NULL; another where the company is specified, and another where both the company and client contact are specified.

I want to pull just one rate out: the one with the 'strongest match' to the company, group and client specified for the job.

(As a bonus, the group for the job might be NULL. A client company and a client contact will always be present, but a group may not be. Not sure if that would affect this though.)

Anyway, you can see my code below, where I'm joining the latest_rate subquery to the main query:
Code:
AND (
            COALESCE(latest_rate.client_company, 1) = COALESCE(job.client_company, 1)
            OR latest_rate.client_company IS NULL
        )
... clearly, this is wrong. It's joining too many rows; do I need some sort of CASE in there, or something? If latest_rate.client_company isn't NULL, join on it, else... only join on it if you don't find any rows that DO match job.client_company?

Code:
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