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 4C5749FA2E6 for ; Wed, 7 Jun 2006 14:51:34 -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 54197-02 for ; Wed, 7 Jun 2006 14:51:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.200]) by postgresql.org (Postfix) with ESMTP id 8A6169FA5E9 for ; Wed, 7 Jun 2006 14:51:28 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id s1so204844nze for ; Wed, 07 Jun 2006 10:51:27 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=qJwMh1CfKr2k8xrplJimnWq3H0Vouzuol4HzzCXZ0bR6CdyRz+cac6YR32Jela69uUjUiCRscxCl2jyQ0oAdKAxrRApoBwgL3Hc61642G9uQSZRAJ8POcdWyzzWOknhFDT5dWbLq/9jCxClntoPs+fR/y3hNvcLdDmuB9SScTNw= Received: by 10.65.105.10 with SMTP id h10mr784877qbm; Wed, 07 Jun 2006 10:51:27 -0700 (PDT) Received: by 10.65.158.4 with HTTP; Wed, 7 Jun 2006 10:51:27 -0700 (PDT) Message-ID: <1b8a973c0606071051l2fee7febj84c7c310b90ff272@mail.gmail.com> Date: Wed, 7 Jun 2006 10:51:27 -0700 From: "Burak Seydioglu" To: "Michael Glaesemann" Subject: Re: Join issue Cc: ken@scottshill.com, operationsengineer1@yahoo.com, pgsql-sql@postgresql.org In-Reply-To: <969FFD67-016F-48D5-98C9-BFD5780C8AFB@seespotcode.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9967_6690518.1149702687427" References: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com> <1149638001.5283.2.camel@localhost> <969FFD67-016F-48D5-98C9-BFD5780C8AFB@seespotcode.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/84 X-Sequence-Number: 25320 ------=_Part_9967_6690518.1149702687427 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline My current solution to the problem is PHP and it - simply put - sucks.... I loop through all the house records for a user using the following query ==================== SELECT * FROM ce_house 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_house.user_id='1' ==================== and then submit an additional query for each result to find the provider rate by comparing ce_contract_created and ce_provider_rate created. ==================== SELECT provider_rate_amount FROM ce_provider_rate WHERE provider_id='".$row["provider_id"]."' AND provider_rate_created<='".$row["contract_created"]."' ORDER BY provider_rate_created DESC LIMIT 1 OFFSET 0 ==================== Table structures: ce_house ==================== house_id provider_id ce_contract ==================== contract_id house_id contract_term contract_created ce_contract_status ==================== contract_status_id contract_id contract_status contract_status_created ce_provider ==================== provider_id provider_name ce_provider_rate ==================== provider_rate_id provider_id provider_rate_amount provider_rate_created Would I violate design principles if I create a new field called "ce_contract_rate" under "ce_contract" and populate it as soon as a new contract is created instead of looking it up from the ce_provide_rate table everytime i need it? Regards, Burak On 6/6/06, Michael Glaesemann wrote: > > > On Jun 7, 2006, at 8:53 , Kenneth B Hill wrote: > > > 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. > > I don't know how using a view would improve performance. However, it > may make the overall query more tractable by encapsulating portions > of it using views. > > Michael Glaesemann > grzm seespotcode net > > > > > ------=_Part_9967_6690518.1149702687427 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline My current solution to the problem is PHP and it - simply put - sucks....

I loop through all the house records for a user using the following query

====================
SELECT * FROM ce_house
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_house.user_id='1'
====================

and then submit an additional query for each result to find the provider rate by comparing ce_contract_created and ce_provider_rate created.

====================
SELECT provider_rate_amount FROM ce_provider_rate WHERE provider_id='".$row["provider_id"]."' AND provider_rate_created<='".$row["contract_created"]."' ORDER BY provider_rate_created DESC LIMIT 1 OFFSET 0
====================


Table structures:

ce_house
====================
house_id
provider_id

ce_contract
====================
contract_id
house_id
contract_term
contract_created

ce_contract_status
====================
contract_status_id
contract_id
contract_status
contract_status_created

ce_provider
====================
provider_id
provider_name

ce_provider_rate
====================
provider_rate_id
provider_id
provider_rate_amount
provider_rate_created

Would I violate design principles if I create a new field called "ce_contract_rate" under "ce_contract"  and populate it as soon as a new contract is created instead of looking it up from the ce_provide_rate table everytime i need it?

Regards,

Burak




On 6/6/06, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Jun 7, 2006, at 8:53 , Kenneth B Hill wrote:

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

I don't know how using a view would improve performance. However, it
may make the overall query more tractable by encapsulating portions
of it using views.

Michael Glaesemann
grzm seespotcode net





------=_Part_9967_6690518.1149702687427--