X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 561999FB1BA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jun 2006 15:10:56 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 36222-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jun 2006 15:10:47 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nz-out-0102.google.com (nz-out-0102.google.com
 [64.233.162.205])
	by postgresql.org (Postfix) with ESMTP id 16C809FB1B9
	for <pgsql-sql@postgresql.org>; Tue,  6 Jun 2006 15:10:47 -0300 (ADT)
Received: by nz-out-0102.google.com with SMTP id s1so1321829nze
	for <pgsql-sql@postgresql.org>; Tue, 06 Jun 2006 11:10:46 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=p1KNtkjBZkA/7jLXWvzZoucP5JzGqOb7E19vx+wlKzg3lcnF4p1DigoEjLf4emV+PcQ+qlGadNeosZchcsiT26hoCT0dTq2I/j+bVQgjn7Z+w2NDrWYoTpavykET3dIK2m6U4xPdZ3dLqlzo0IlXob2cBC4nNJMiIi1m+KKFFNA=
Received: by 10.65.51.14 with SMTP id d14mr4873341qbk;
	Tue, 06 Jun 2006 11:10:46 -0700 (PDT)
Received: by 10.65.158.4 with HTTP; Tue, 6 Jun 2006 11:10:46 -0700 (PDT)
Message-ID: <1b8a973c0606061110r5deece44q485eeffc6108485d@mail.gmail.com>
Date: Tue, 6 Jun 2006 11:10:46 -0700
From: "Burak Seydioglu" <buraks78@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Join issue
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_73493_18002231.1149617446183"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/70
X-Sequence-Number: 25306

------=_Part_73493_18002231.1149617446183
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_73493_18002231.1149617446183
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,<br><br>I have one to many (from left to right) relationships among the below tables in my database<br><br>user -&gt; house -&gt; contract -&gt; contract status<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br><br><br><br>Also, a single house has a single provider and the provider has multiple
rates inside the provider_rate table in chronological order.<br><br>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.
<br><br>(Please see section marked with &gt;&gt; below). <br><br>Here is the latest version of the SQL and it does not work (see ce_contract.contract_created)<br><br>SELECT ce_house.house_id,ce_contract.contract_duration,ce_contract_status.contract_status FROM ce_house 
<br>LEFT JOIN ce_provider_rate ON ce_provider_rate.provider_id=ce_house.provider_id <br>LEFT JOIN ce_contract ON ce_house.house_id=ce_contract.house_id <br>LEFT JOIN ce_contract_status ON ce_contract.contract_id=ce_contract_status.contract_id 
<br>WHERE <br>ce_contract.contract_id IN (SELECT MAX(ce_contract.contract_id) FROM ce_contract GROUP BY ce_contract.house_id) <br>AND <br>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) 
<br>AND <br>&gt;&gt; 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&lt;=ce_contract.contract_created) <br>AND <br>ce_house.house_id='1'
<br><br><br>I would appreciate any insight to help me solve this issue...<br><br>Burak<br>

------=_Part_73493_18002231.1149617446183--