Join issue

Hi,<br /><br />I have one to many (from left to right) relationships among the below tables in my database<br /><br
/>user-> house -> contract -> contract status<br />               |<br /><br /><br /><br />Also, a single
househas a single provider and the provider has multiple rates inside the provider_rate table in chronological
order.<br/><br />I have a query to return the latest contract and contract status for a house... What i am trying to do
isto get the rate of electricity for the latest contract... I am trying to retrieve the latest provider rate before a
contractis signed. <br /><br />(Please see section marked with >> below). <br /><br />Here is the latest version
ofthe SQL and it does not work (see ce_contract.contract_created)<br /><br />SELECT
ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_statusFROM ce_house <br />LEFT JOIN
ce_provider_rateON ce_provider_rate.provider_id=ce_house.provider_id <br />LEFT JOIN ce_contract ON
ce_house.house_id=ce_contract.house_id<br />LEFT JOIN ce_contract_status ON
ce_contract.contract_id=ce_contract_status.contract_id<br />WHERE <br />ce_contract.contract_id IN (SELECT
MAX(ce_contract.contract_id)FROM ce_contract GROUP BY ce_contract.house_id) <br />AND <br
/>ce_contract_status.contract_status_idIN (SELECT MAX(ce_contract_status.contract_status_id) FROM ce_contract_status
GROUPBY ce_contract_status.contract_id) <br />AND <br />>> 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)<br />AND <br />ce_house.house_id='1' <br /><br
/><br/>I would appreciate any insight to help me solve this issue...<br /><br />Burak<br />