From: operationsengineer1@yahoo.com
To: Burak Seydioglu <buraks78@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Join issue
Date: Tue, 6 Jun 2006 15:33:09 -0700 (PDT)
Message-ID: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com> (raw)
In-Reply-To: <1b8a973c0606061110r5deece44q485eeffc6108485d@mail.gmail.com>
> 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
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: operationsengineer1@yahoo.com, buraks78@gmail.com
Subject: Re: Join issue
In-Reply-To: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com>
* 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