From: Burak Seydioglu <buraks78@gmail.com>
To: Michael Glaesemann <grzm@seespotcode.net>
Cc: ken@scottshill.com
Cc: operationsengineer1@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue
Date: Wed, 7 Jun 2006 10:51:27 -0700
Message-ID: <1b8a973c0606071051l2fee7febj84c7c310b90ff272@mail.gmail.com> (raw)
In-Reply-To: <969FFD67-016F-48D5-98C9-BFD5780C8AFB@seespotcode.net>
References: <20060606223309.69534.qmail@web33314.mail.mud.yahoo.com>
<1149638001.5283.2.camel@localhost>
<969FFD67-016F-48D5-98C9-BFD5780C8AFB@seespotcode.net>
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
>
>
>
>
>
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, grzm@seespotcode.net, ken@scottshill.com, operationsengineer1@yahoo.com
Subject: Re: Join issue
In-Reply-To: <1b8a973c0606071051l2fee7febj84c7c310b90ff272@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