Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: can this be done?

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    91

    Re: can this be done?

    Originally posted by allang
    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
    The reason why it takes a long time is because:

    1. you left out primary keys on all your tables
    2. you should be joining on fields that have indexes
    associated with them, ideally the primary key field
    3. you're joining on fields that are varchars, when
    joins go much faster if you join on fields that are integers.
    You should consider adding an aritificial key field to
    tables B and C (id INT(4) NOT NULL AUTO_INCREMENT),
    and join on this field instead.

    Go back and redefine your tables so that they fix
    the above problems and your joins will go quickly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •