Re: 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, having battled a couple big queries over that
last week, my first piece of advice is simplify the
query to the area that is giving you the trouble... 
iow, set up your query such that it *only* pulls the
latest contract rate and leave all the noise out (you
already know how to do the noise and can add it back
in later).

the query you want seems very similar to a subquery i
recently put together.

instead of the latest date contract rate by house, i
needed the latest value of a boolean by inspection
node.

a simplified version of the query i used is here...  

http://www.rafb.net/paste/results/m322aH47.html

of course, you have to adjust for different table
relations.

the query, as it stands, lists *all* latest result
grouped by inspection_id.

you'll be adding...

AND ce_house.house_id='1' and add associated FROM
tables and WHERE equations.

you can specify

AND ce_house.house_id='1'

to limit the results further.

this is my best try at the partial query given i can't
totally understand your table structure...

http://www.rafb.net/paste/results/MlJUrO80.html

if i guessed wrong, adjust for your actual table
structure.

good luck.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com