Re: Join issue

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(at)seespotcode(dot)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
>
>
>
>
>