From: Kenneth B Hill <ken@scottshill.com>
To: operationsengineer1@yahoo.com
Cc: Burak Seydioglu <buraks78@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue
Date: Tue, 06 Jun 2006 16:53:21 -0700
Message-ID: <1149638001.5283.2.camel@localhost> (raw)
In-Reply-To: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com>
References: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ken@scottshill.com, operationsengineer1@yahoo.com, buraks78@gmail.com
Subject: Re: Join issue
In-Reply-To: <1149638001.5283.2.camel@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox