Hi,

I have a query ...

select c.month
from summary c, summary v
where c.location = 'Carlisle' and v.location = 'Newcastle'
and c.minTemp > v.minTemp and c.maxTemp > v.maxTemp ;

Which finds the months where the max and min temp were higher than carlisle

However when it is executed the returned results are incorrect. It works fine when the min and max temp are ints, however once i make them decimal(3,2) format the query messes up...

Any ideas how I can sort this out ?

Thanks Chris

When you say the query messes up, what exactly happens, are the results invalid or is an error thrown. What error or describe the results.

It should also be noted than since you do not join summary c & summary v by month, this query will return any month in c that had a higher temperature than any month in v. Is this intended?

For example given:
location month minTemp maxTemp
Carlisle July 50 80
Newcastle July 60 90
Newcastle December 20 30

July will appear since Carlisle's July was warmer than Newcastle's December

Thanks for the reply...

By messing up I mean extra results are returned that are clearly wrong. However this only happens when the min / max temp fields are stored as decimal(3,2) format.

Thanks for pointing out the other problem, I didn't see that. I think this is probably what is causing the extra months to be returned by the query, nothing to do with the format.

How can I use JOIN to make sure the temps compared are from the same month ?

Again, thanks for the reply

Chris

You have two methods for making sure the months line up, just do another equality test in the where clause (c.month = v.month):

select c.month
from summary c, summary v
where c.month = v.month
and c.location = 'Carlisle' and v.location = 'Newcastle'
and c.minTemp > v.minTemp and c.maxTemp > v.maxTemp ;

Or change to a join syntax:

select c.month
from summary c
INNER JOIN
summary v
ON c.month = v.month
and c.minTemp > v.minTemp
and c.maxTemp > v.maxTemp
where
c.location = 'Carlisle' and v.location = 'Newcastle' ;

Cheers mate,

Got it all sorted now...

Thanks for the help !

Chris