Re: Join issue - Mailing list pgsql-sql
| From | Burak Seydioglu |
|---|---|
| Subject | Re: Join issue |
| Date | |
| Msg-id | 1b8a973c0606071051l2fee7febj84c7c310b90ff272@mail.gmail.com Whole thread Raw |
| In response to | Re: Join issue (Michael Glaesemann <grzm@seespotcode.net>) |
| Responses |
Re: Join issue
|
| List | pgsql-sql |
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
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
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
====================
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