Re: Join issue

That looks like a very complex query. I would like to suggest that you
try doing some nesting queries via a SQL script. Make a "view" with a
query, then perform a query using the view, etc. , then drop all views
in the SQL script. This may make the entire operation perform faster.

-Ken

On Tue, 2006-06-06 at 15:33 -0700, operationsengineer1(at)yahoo(dot)com wrote:
> > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster