how to subtract values from two tables

[shadkeene]

Hi,
I have two tables that have values that I want to compare.
So far, I've been testing my database design to see if it can achieve what I'd like to do.
I'm sure my design is far from perfect, but I've been able to join tables so far.

So, I have two tables that I've been using timestamp as their primary key. I'm trying to subtract values from columns shft_summary.CANX and coldseason.LOCLDTIME by using the following:

select (CANX from shft_summary where date_time='2007-11-06 13:00:00')-(LOCLDTIME from coldseason where date_time='2007-11-06 07:50:00');

Am I missing something? Do I need to join the two tables before subtracting?

Oh, and another thing...the data type in shft_summary.CANX and coldseason.LOCLDTIME are char. Am I correct that mysql still subtracts char datatypesas long as the content of the column is numeric?
Thanks for any help.

[guelphdad]

can you show some sample data from your tables and what you want to achieve?

[shadkeene]

Sure...as you might notice I have at most two entries per day for each table (shft_summary and coldseason). I've made the timestamp now() the primary key. I'd like to subtract values from columns 45rate and LOCLDTIME that occured during the same part of the day. For instance, since there might be two rows for one date, I'd like to subtract a 45rate value that occurred before 15:00 from a LOCLDTIME that occurred before 15:00 on the same day. Eventually, I'd love to take averages of the difference between 45rate and LOCLDTIME that occur on the same part of the same day. If this doesn't make sense, I can clarify. In the following case, I'd like to subtract 2030 from 2200. Thanks for any help,
Shad


The following two columns are from table shft_summary;

date_time | 45rate |
+---------------------+
| 2007-10-28 13:00:00 |
| 2007-10-29 13:00:00 |
| 2007-10-29 20:00:00 |
| 2007-10-30 20:00:00 |
| 2007-10-31 20:00:00 |
| 2007-11-02 13:00:00 |
| 2007-11-04 13:00:00 |
| 2007-11-04 21:08:34 |
| 2007-11-05 12:20:15 |
| 2007-11-06 13:00:00 | 2200
| 2007-11-06 20:58:01 |
| 2007-11-07 13:00:00 |
+---------------------+


The following two columns are from coldseason

+---------------------
| date_time | LOCLDTIME |
+---------------------
| 2007-10-28 08:00:00
| 2007-10-29 08:00:00
| 2007-10-29 16:00:00
| 2007-10-30 16:00:00
| 2007-10-31 16:00:00
| 2007-11-02 08:00:00
| 2007-11-04 09:26:37
| 2007-11-04 21:08:01
| 2007-11-05 07:58:03
| 2007-11-06 07:50:00 2030
| 2007-11-06 15:21:48
| 2007-11-07 07:50:00
| 2007-11-07 16:31:25
+---------------------