problem fetching the total number of rows newbie

B

bhat

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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top