Thread: Join issue

Join issue

From

"Burak Seydioglu"

Date:

06 June 2006, 18:10:57

Hi,<br /><br />I have one to many (from left to right) relationships among the below tables in my database<br /><br
/>user-> house -> contract -> contract status<br />               |<br /><br /><br /><br />Also, a single
househas 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
isto get the rate of electricity for the latest contract... I am trying to retrieve the latest provider rate before a
contractis signed. <br /><br />(Please see section marked with >> below). <br /><br />Here is the latest version
ofthe 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_statusFROM ce_house <br />LEFT JOIN
ce_provider_rateON 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_idIN (SELECT MAX(ce_contract_status.contract_status_id) FROM ce_contract_status
GROUPBY ce_contract_status.contract_id) <br />AND <br />>> 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)<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 />

Re: Join issue

From

Andrew Sullivan

Date:

06 June 2006, 18:27:52

On Tue, Jun 06, 2006 at 11:10:46AM -0700, Burak Seydioglu wrote:
> 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.

I don't have a proposal to rewrite, but I suspec this is going to be
easier with a query to get the latest provider rate in the FROM
clause.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."    --Damien Katz

Re: Join issue

From

Date:

06 June 2006, 22:33:20

> 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, having battled a couple big queries over that
last week, my first piece of advice is simplify the
query to the area that is giving you the trouble... 
iow, set up your query such that it *only* pulls the
latest contract rate and leave all the noise out (you
already know how to do the noise and can add it back
in later).

the query you want seems very similar to a subquery i
recently put together.

instead of the latest date contract rate by house, i
needed the latest value of a boolean by inspection
node.

a simplified version of the query i used is here...  

http://www.rafb.net/paste/results/m322aH47.html

of course, you have to adjust for different table
relations.

the query, as it stands, lists *all* latest result
grouped by inspection_id.

you'll be adding...

AND ce_house.house_id='1' and add associated FROM
tables and WHERE equations.

you can specify

AND ce_house.house_id='1'

to limit the results further.

this is my best try at the partial query given i can't
totally understand your table structure...

http://www.rafb.net/paste/results/MlJUrO80.html

if i guessed wrong, adjust for your actual table
structure.

good luck.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Re: Join issue

From

Kenneth B Hill

Date:

06 June 2006, 23:58:33

That looks like a very complex query. I would like to suggest that you
try doing some nesting queries via a SQL script. 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.

-Ken

On Tue, 2006-06-06 at 15:33 -0700, operationsengineer1@yahoo.com wrote:
> > 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, having battled a couple big queries over that
> last week, my first piece of advice is simplify the
> query to the area that is giving you the trouble... 
> iow, set up your query such that it *only* pulls the
> latest contract rate and leave all the noise out (you
> already know how to do the noise and can add it back
> in later).
> 
> the query you want seems very similar to a subquery i
> recently put together.
> 
> instead of the latest date contract rate by house, i
> needed the latest value of a boolean by inspection
> node.
> 
> a simplified version of the query i used is here...  
> 
> http://www.rafb.net/paste/results/m322aH47.html
> 
> of course, you have to adjust for different table
> relations.
> 
> the query, as it stands, lists *all* latest result
> grouped by inspection_id.
> 
> you'll be adding...
> 
> AND ce_house.house_id='1' and add associated FROM
> tables and WHERE equations.
> 
> you can specify
> 
> AND ce_house.house_id='1'
> 
> to limit the results further.
> 
> this is my best try at the partial query given i can't
> totally understand your table structure...
> 
> http://www.rafb.net/paste/results/MlJUrO80.html
> 
> if i guessed wrong, adjust for your actual table
> structure.
> 
> good luck.
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Re: Join issue

From

Michael Glaesemann

Date:

07 June 2006, 00:05:43

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

Re: Join issue

From

"Burak Seydioglu"

Date:

07 June 2006, 17:51:34

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

Re: Join issue

From

Date:

07 June 2006, 19:04:30

> 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

if i'm reading this right, a house can only have one
provider, but a house can have multiple contracts.  at
first glance, i'd think the contracts should be
associated with the provider, not the house.  if true,
you need to update your table structure.

is the rate associated with the contract (i'd assume
so with limited informationthink so) or the provider
(the way you have it set up)?

this is how i envision the table structure (granted, i
have limited information so i coul dbe way off
base)...

ce_house
====================
house_id

ce_provider
====================
provider_id
** house_id (fkey) **
provider_name

ce_contract
====================
contract_id
** provider_id (fkey) **
contract_term
contract_created

ce_contract_status
====================
contract_status_id
contract_id (fkey)
contract_status
contract_status_created

** ce_contract_rate **
====================
contract_rate_id
contract_id (fkey)
contract_rate_amount
contract_rate_created

i also assume, based on your structure, that you can
have multiple contract statuses for a given contract. 
if not,you could probably delete that table and just
add contract_status and contract_status_created to
ce_contract.

i'm sorry if i'm way off base, but i'm trying to wrap
my head around the table strcuture, but i'm not
necessarily familiar with all the business rules that
created the structure - so i may be way off base here.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com