From: Burak Seydioglu <buraks78@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Join issue
Date: Tue, 6 Jun 2006 11:10:46 -0700
Message-ID: <1b8a973c0606061110r5deece44q485eeffc6108485d@mail.gmail.com> (raw)
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
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: buraks78@gmail.com
Subject: Re: Join issue
In-Reply-To: <1b8a973c0606061110r5deece44q485eeffc6108485d@mail.gmail.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