Join issue

Hi,

I have one to many (from left to right) relationships among the below tables
in my database

user -> house -> contract -> contract status
|

Also, a single house has a single provider and the provider has multiple
rates inside the provider_rate table in chronological order.

I have a query to return the latest contract and contract status for a
house... What i am trying to do is to get the rate of electricity for the
latest contract... I am trying to retrieve the latest provider rate before a
contract is signed.

(Please see section marked with >> below).

Here is the latest version of the SQL and it does not work (see
ce_contract.contract_created)

SELECT
ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_status
FROM ce_house
LEFT JOIN ce_provider_rate ON
ce_provider_rate.provider_id=ce_house.provider_id
LEFT JOIN ce_contract ON ce_house.house_id=ce_contract.house_id
LEFT JOIN ce_contract_status ON
ce_contract.contract_id=ce_contract_status.contract_id
WHERE
ce_contract.contract_id IN (SELECT MAX(ce_contract.contract_id) FROM
ce_contract GROUP BY ce_contract.house_id)
AND
ce_contract_status.contract_status_id IN (SELECT
MAX(ce_contract_status.contract_status_id) FROM ce_contract_status GROUP BY
ce_contract_status.contract_id)
AND
>> ce_provider_rate.provider_rate_id IN (SELECT
MAX(ce_provider_rate.provider_rate_id) FROM ce_provider_rate WHERE
ce_provider_rate.provider_rate_created<=ce_contract.contract_created)
AND
ce_house.house_id='1'

I would appreciate any insight to help me solve this issue...

Burak