need help.. urgent!

I need to write a select statement to get the following results.
I tried using joins, but it just takes all my cpu time, and takes forever to process.



TABLES:
-----------------
(about 500 records)
CREATE TABLE `A` (
  `ACCNT_CODE` varchar(50) default NULL,
  `TRANS_DATE` datetime default NULL,
  `AMOUNT` double default NULL,
  `TREFERENCE` varchar(50) default NULL,
  `OTHER_AMT` double default NULL,
  `TRANS_ID` int(11) NOT NULL auto_increment,
  KEY `TRANS_ID` (`TRANS_ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

(about 50000 records)
CREATE TABLE `B` (
  `ACCNT_CODE` varchar(255) default NULL,
  `ACCNT_NAME` varchar(255) default NULL,
  `AN_A2` varchar(255) default NULL,
  `BADFLAG` varchar(50) default 'N',
  `DATE_ACTION` datetime default '2001-01-19 00:00:00',
) TYPE=MyISAM;

(about 5000 records)
CREATE TABLE `C` (
  `CATEGORY` varchar(255) default NULL,
  `CODE` varchar(255) default NULL,
  `NAME` varchar(255) default NULL
) TYPE=MyISAM;
------------------------




RESULT NEEDED:
---------------------
ACCNT_CODE,

ACCNT_NAME,

TOTAL_AMOUNT,
SUM(A.AMOUNT) GROUP BY ACCNT_CODE,

OVERDUE_AMNT,
SUM(A.AMOUNT) WHERE TRANS_DATE > 'x' GROUP BY ACCNT_CODE

DATE_ACTION,

NAME,
C.CODE=B.AN_2 AND C.CATEGORY='x'
----------------------------------------------


WHERE A.ACCNT_CODE=B.ACCNT_CODE
GROUP BY ACCNT_CODE


Anyone has a solution?

Thanks

Can you post the select statement that you tried???

-Vijay G

Well, you might be better off if you created real indexes in your columns. You don't have ANY columns where a numeric (read: easily sorted and compared) value serves as a unique index into that column.

When you're combining three tables of up to 50000 rows per table, and you're performing all your comparisons in VARCHAR fields, you're wasting a LOT of space and requiring a LOT of computing.



Take care,

Nik
http://www.bigaction.org/