Subject: Re: Join issue
From: Kenneth B Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: operationsengineer1@yahoo.com
Cc: Burak Seydioglu <buraks78@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com>
References: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com>
Content-Type: text/plain
Organization: Scotts Hill Associates, Inc.
Date: Tue, 06 Jun 2006 16:53:21 -0700
Message-Id: <1149638001.5283.2.camel@localhost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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@yahoo.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