[SQL] Join issue

[Burak Seydioglu]

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