Hi folks,
I'm stumped on a SQL query. Using SQL Server 7.0.
I have two tables - one with a lot of detail info, and one as a cross
reference. The detail table (msgs) has a primary key (message_id) and
an e-mail address (emailAddress), plus other stuff that doesn't matter
for now. The cross reference table (xref_forwards) has
originalMessage_ID and newMessage_ID.
When someone forwards an e-mail message to another person, the system
currently checks to see if the new address (the forward to address) is
present in the system. If so, it inserts a record into the cross
reference table with the message_id of the sender, and the existing
message_id of the recipient. If the new address is not present, it adds
a record for that address, grabs the new message_ID, and inserts a
record into the cross reference table.
Pretty straightforward stuff so far.
What's beating me up is writing a query that will return the original
sender's email address and the people that person forwarded the message
to, with a count of how many mails were forwarded per original email
address. So far I have:
SELECT a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS
SenderAddress, d.EMailAddress AS NewAddress, COUNT(a.originalMessage_ID)
AS totalforwards
FROM xref_forwarding a INNER JOIN
xref_forwarding b ON a.originalMessage_ID =
b.newMessage_ID INNER JOIN
msgs c ON a.originalMessage_ID = c.Message_ID
INNER JOIN
msgs d ON a.newMessage_ID = d.Message_ID
GROUP BY a.originalMessage_ID, a.newMessage_ID, c.EMailAddress,
d.EMailAddress
ORDER BY a.originalMessage_ID
This seems to work fine. Here's example output from the above query:
(in all the output, the domain names are changed so I don't get
screenscraped by spammers - the numbers all match up with the original
domains).
a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress,
d.EMailAddress AS NewAddress
663352 663352 [EMAIL PROTECTED] [EMAIL PROTECTED]
663352 670110 [EMAIL PROTECTED] [EMAIL PROTECTED]
663352 671038 [EMAIL PROTECTED] [EMAIL PROTECTED]
663352 714066 [EMAIL PROTECTED] [EMAIL PROTECTED]
663352 714187 [EMAIL PROTECTED] [EMAIL PROTECTED]
663353 670109 [EMAIL PROTECTED] [EMAIL PROTECTED]
663353 670110 [EMAIL PROTECTED] [EMAIL PROTECTED]
663353 714066 [EMAIL PROTECTED] [EMAIL PROTECTED]
670110 714046 [EMAIL PROTECTED] [EMAIL PROTECTED]
670110 714188 [EMAIL PROTECTED] [EMAIL PROTECTED]
671038 663352 [EMAIL PROTECTED] [EMAIL PROTECTED]
671038 663353 [EMAIL PROTECTED] [EMAIL PROTECTED]
671038 714186 [EMAIL PROTECTED] [EMAIL PROTECTED]
714066 671038 [EMAIL PROTECTED] [EMAIL PROTECTED]
714066 714181 [EMAIL PROTECTED] [EMAIL PROTECTED]
714066 714182 [EMAIL PROTECTED] [EMAIL PROTECTED]
714066 714183 [EMAIL PROTECTED] [EMAIL PROTECTED]
714066 714184 [EMAIL PROTECTED] [EMAIL PROTECTED]
714066 714185 [EMAIL PROTECTED] [EMAIL PROTECTED]
714188 671036 [EMAIL PROTECTED] [EMAIL PROTECTED]
714188 671038 [EMAIL PROTECTED] [EMAIL PROTECTED]
714188 714187 [EMAIL PROTECTED] [EMAIL PROTECTED]
The counts add up fine - I tested manually, writing down who sent mail
to whom.
When I add a count column the results are still the same, but the counts
are wrong:
SELECT a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS
SenderAddress, d.EMailAddress AS NewAddress, COUNT(a.originalMessage_ID)
AS TotalForwards
FROM xref_forwarding a INNER JOIN
xref_forwarding b ON a.originalMessage_ID =
b.newMessage_ID INNER JOIN
msgs c ON a.originalMessage_ID = c.Message_ID
INNER JOIN
msgs d ON a.newMessage_ID = d.Message_ID
GROUP BY a.originalMessage_ID, a.newMessage_ID, c.EMailAddress,
d.EMailAddress
ORDER BY a.originalMessage_ID
outputting:
a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress,
d.EMailAddress AS NewAddress, COUNT(a.originalMessage_ID)
663352 663352 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
663352 670110 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
663352 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
663352 714066 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
663352 714187 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
663353 670109 [EMAIL PROTECTED] [EMAIL PROTECTED] 1
663353 670110 [EMAIL PROTECTED] [EMAIL PROTECTED] 1
663353 714066 [EMAIL PROTECTED] [EMAIL PROTECTED] 1
670110 714046 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
670110 714188 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
671038 663352 [EMAIL PROTECTED] [EMAIL PROTECTED] 3
671038 663353 [EMAIL PROTECTED] [EMAIL PROTECTED] 3
671038 714186 [EMAIL PROTECTED] [EMAIL PROTECTED] 3
714066 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
714066 714181 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
714066 714182 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
714066 714183 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
714066 714184 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
714066 714185 [EMAIL PROTECTED] [EMAIL PROTECTED] 2
714188 671036 [EMAIL PROTECTED] [EMAIL PROTECTED] 1
714188 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 1
714188 714187 [EMAIL PROTECTED] [EMAIL PROTECTED] 1
As you can see, the last three rows, the sender [EMAIL PROTECTED]
should count 3 forwards. I thought that by grouping on the original
message_id the counts would roll up as well.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
