problem fetching the total number of rows newbie

Discussion in 'Perl Misc' started by bhat, Sep 22, 2003.

  1. bhat

    bhat Guest

    Hello,
    I am working on a mail server program ie my program reads the mail
    from the mail server and dumps into the database(MYSQL). All the
    messages are being kept in the table TB_MESSAGE. Table is being
    designed as below
    COUNT_ID PRIMARY KEY, --counter to keep track of the number of
    messages
    THREAD_ID -- used to identify the grouping of related messages
    SUBJECT,
    AFROM,
    ATO,
    MESSAGE_INDICATOR #--indicates whether it is a new message or not
    REPLIED_OR_NOT #--zero for new messages
    MESSAGE_STATUS
    etc.

    #--example(data is being stored as below
    COUNT_ID THREAD_ID MESSAGE_INDICATOR
    100 THOO23 1
    101 THOO24 1
    102 THOO25 0
    103 THOO23 0
    104 THOO23 0


    i want to get new messages from the TB_MESSAGE and also the count of
    number of related messages of this
    message.

    ie my query should get the results as below
    COUNT_ID COUNT(THREAD_ID)
    100 3
    101 1

    How should i write the query ? Actually i was doing it as below

    #--the below query runs for 2 times and gets 100 and 101.
    SELECT COUNT_ID,AFROM,SUBJECT,THREAD_ID FROM TB_MESSAGE WHERE
    MESSAGE_INDICATOR=1 GROUP BY THREAD_ID ORDER BY SEARCH_DATE
    DESC,COUNT_ID DESC;
    while()
    {
    #--i get the count(*)
    #--get the thread_id form the outer query and use it in the below
    query.
    SELECT COUNT(*) FROM TB_MESSAGE WHERE THREAD_ID=?
    {

    }



    }

    #--if i use the above scenario it takes a long time to display the
    messges. Since the second query is being executed throughout the
    while loop. Can i write a single query to do the above functinality.

    #--the below query doesn't work.
    SELECT A.THREAD_ID,COUNT(*) FROM TB_MESSAGE A,TB_MESSAGE B WHERE
    A.COUNT_ID=B.COUNT_ID AND A.MESSAGE_INDICATOR=1 AND A.REPLIED_OR_NOT=0
    AND (A.MESSAGE_STATUS=1 OR A.MESSAGE_STATUS=4 OR A.MESSAGE_STATUS=2)
    GROUP BY B.THREAD_ID;

    Could any one help me in building this query.

    Thanks in Advance,
    Regards,
    Shirley.
    bhat, Sep 22, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?ZGF2aWQ=?=

    How to get the total number of rows in a Dataset?

    =?Utf-8?B?ZGF2aWQ=?=, May 3, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    17,287
    Yunus Emre ALPĂ–ZEN [MCAD.NET]
    May 3, 2005
  2. thomson
    Replies:
    1
    Views:
    793
    Brock Allen
    Jun 11, 2005
  3. Praveen homkar via JavaKB.com

    how to get total number of rows contained in a resultset

    Praveen homkar via JavaKB.com, Aug 5, 2005, in forum: Java
    Replies:
    1
    Views:
    5,333
  4. Craig Buchanan
    Replies:
    1
    Views:
    2,463
    Reggie
    Oct 2, 2008
  5. yonialhadeff

    Group By Rows and Total Rows

    yonialhadeff, Jan 23, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    846
    Eliyahu Goldin
    Jan 23, 2007
Loading...

Share This Page