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.
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.